I have an OLTP database and alot of users that is querying against that.
I am thinking of a way to store a more denormalized version of some tables in another database, and use that for a search function that we have.
The problem is that the base tables are updated all the time with new and new data. And the search cannot have that long delay but a delay of 5-10 minutes for updates/insert is ok. deletes though cannot have any delay.
I was thinking of a SCD-setup and when a delete goes against the OLTP then we also run a delete against the SCD-database. But is it possible to create a SCD-database and have it update every 5-10 minutes?
I have also been thinking of a queue-table (and store updates/inserts) in the OLTP-database and then run a query every 5 min against the queue-table and update the denormalized version of the table. But isnt this a longer way? Why not update the denormalized version of the table directly when the OLAP-tables is updated?
I have also been thinking of to truncate and then fill the denormalized version every 5-10 minute from the OLTP-version. Maybe have two tables and after the fill is complete do a rename of the two tables, so that we wont lock the table that long while filling it up with the new data.
The denormalized version would contain around 200k rows.
I have started working with MS SQL Server 2000 recently. I have a scenario in which I require to know within the given period of time ( say 5 mins) , which all tables from a particular database got modified. I do not want to write a trigger for each and every table for all the 13 databases , my application deals with. I have even tried the following query: declare @curdate datetime select @curdate=getdate() select name, refdate from sysobjects where xtype = 'U' and refdate =@curdate
But nope it does not help me, since refdate is something else. Can anybody tell me how can you figure out from sysobjects when was a particular object last accessed , even this would serve my purpose.
I have trying to get this done via profiler. My applications api's connect to the database under some credentials which i do not know since I do not have access to source code ( i am doing black box testing). So I can't even put a trace on one particular user account. What I am doing currently is trapping all store procedure events..., but then its too much of work...
Hence I wanted to know , is there any way out for the situation where given a database name and a time span we can find out the tables modified/accessed within that time span from that database ???
have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?set ANSI_NULLS OFF set QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[mainSearch] @startRowIndex int, @maximumRows int, @rowCount int out, @countedRow int, @QUERY nvarchar(400) AS SELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM ( SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,* FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL ON ADTBL._ID = KEY_TBL.[KEY] Where (_DELETIONSTATUS=0)
) AS RANKEDADDS
WHERE ROWRANK > @startRowIndex AND ROWRANK <= @startRowIndex + @maximumRows -1
if(@countedRow < 1) SET @rowCount = ( SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL ON ADTBL._ID = KEY_TBL.[KEY] Where _DELETIONSTATUS=0 ) else SET @rowCount = @countedRow
I intend to develop a web based application, which uses SQL server 2005 at back end and Visual studio 2.0 as front end.
Application serves two functionalities
Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.
Scenario: The above requirement can be complemented by following example
Consider we have a bank database which has its existing customer DataBase having containing attributes like Name, Age, and Profession e.t.c.
Now if some new customer want to open a new account in bank, then bank officials want to know whether the
new customer is one of the existing customer or not(without asking to customer itself).
System should be able to detect the combination of name also i.e if we enter "Jhon" from front end .net interface
then application should be able generate all list of all customer having "Jhon" as part of their name at any location(firstname, middlename, lastname).
Requirement 2: If some time change is detected in bank's extisting customer's DataBase then each record of this DataBase is searched against a external dataBase(having almost 2 -3 million records).
Scenario: The above requirement can be complemented by following example
If new user is added to bank's existing customer database(database change) then this new updated database's every record is serarched against another bank's database.
I would like to hear experts voice for database design of such application for optimal performance,and types of searches I should look for application.
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy' http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time! And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy. I just simply could NOT get it to work. You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question: Would you like to copy the file to your project and modify the connection? - NO ( no matter what - ANSWER NO ! - Absolutely NO ) Then select the tables you want in the DataSet. and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details Now Drag the table name onto the form.
The form is then populated with a Navigation control and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there. Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets, (a copy/snapshot of the dataset in memory and NOT directly linked to the database) the dataset will reflect all changes made when moving around the detached datasets. YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM. Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks, Barry G. Sumpter
Currently working with: Visual Basic 2005 Express SQL Server 2005 Express
Developing Windows Forms with 101 Samples for Visual Basic 2005 using the DataGridView thru code and every development wizard I can find within vb.net unless otherwise individually stated within a thread.
hi all, if i have a comma delimited string and want to insert each delimited substring into a table which of the following way is faster?pass the whole string into the a stored procedure and loop through the delimited string and pick out the substring and insert into the table orloop and pass the substring into a stored procedure and insert N times?or any other better ways someone could suggest me to do thanks!
I was just wondering if this can be done any faster? code-wise that is...
Don't mind the converts, can't do without them, as the data discipline for the source table isn't always reliable, while I have to be absolutely sure the destination data ends in the required format.
I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...
I'm sonewhat new to MS SQL Server and I'm wondering about which of thefollowing two queries would be faster:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSELECT@ResidencesBuilt = SUM(CASE WHEN BuildingType = 0 THEN Built END),@BarracksBuilt = SUM(CASE WHEN BuildingType = 1 THEN Built END),@AirBaysBuilt = SUM(CASE WHEN BuildingType = 2 THEN Built END),@NuclearPlantsBuilt = SUM(CASE WHEN BuildingType = 3 THEN Built END),@FusionPlantsBuilt = SUM(CASE WHEN BuildingType = 4 THEN Built END),@StarMinesBuilt = SUM(CASE WHEN BuildingType = 5 THEN Built END),@TrainingCampsBuilt = SUM(CASE WHEN BuildingType = 6 THEN Built END),@FactoriesBuilt = SUM(CASE WHEN BuildingType = 7 THEN Built END)FROM BuildingsGROUP BY kdIDHAVING kdID = 2902Or:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSET @ResidencesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 0AND kdID = 2902)SET @BarracksBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 1 ANDkdID = 2902)SET @AirBaysBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 2 ANDkdID = 2902)SET @NuclearPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =3 AND kdID = 2902)SET @FusionPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 4AND kdID = 2902)SET @StarMinesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 5AND kdID = 2902)SET @TrainingCampsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =6 AND kdID = 2902)SET @FactoriesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 7AND kdID = 2902)The data source is:kdID BuildingType Built2902 6 02902 7 02902 4 02902 0 802902 2 02902 1 52902 3 402902 5 10Or:CREATE TABLE [dbo].[Buildings] ([kdID] [int],[BuildingType] [tinyint],[Built] [int])INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 0, 80)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 1, 5)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 2, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 3, 40)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 4, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 5, 10)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 6, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 7, 0)Analyzer says the first would be faster, but it has a lot of SUM()'s andwhatnot so I'm not too sure about this. There are also about 1000 rows inthe actual Buildings table. This will be a part of a stored procedure.
I want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds).
Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.
ApplicantID FirstName LastName CompanyName Line1 Line2 City
State Zip PhoneNum
Owner
OwnerID FirstName LastName CompanyName Line1 Line2 City
State Zip PhoneNum
Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is 1. how do i get the last three numbers to increment each time a new application is created? 2. how do i retrieve the last record in the table??? 3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???
Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.
Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).
I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.
This info in this table should:
Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)
Be updated: if the customer order has been updated (i.e. the commission was changed)
Be inserted: if the order exists but the order number is not in the new table.
hi i have written a procedure for stock report. its working fine. please go through the sp and give me some Suggestions. please tell me where i need to improve my code. thanks
Note: User is required to execute this procedure daily. i am taking the sum of issues,purchases,returns,physical adjustments for each and every product from last updated date to today's date and storing it in a table i,e stock_Dump. from this table i generate the date wise stock report
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
CREATE PROCEDURE dbo.spUpdateStock @strReturn varchar(70) output AS BEGIN declare @maxDt smalld atetime if exists(Select * from Stock_Dump where Txn_Date= Convert(varchar,Getdate(),101)) BEGIN set @strReturn='Stock Table already generated for the day. cannot generate it again' END
ELSE BEGIN TRUNCATE TABLE Stock_Dump_Temp select @maxDt=max(Txn_Date) from Stock_Dump /* insert (Opening stock) Closing stock for all all the products from last max Date*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date, Closing_Stock as Opening_Stock , 0,0,0,0,0,0,0 from Stock_Dump Where Txn_Date=Convert(varchar,@maxDt,101) /* Issues*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0, Sum(Qty) as Issue_Qty,0,0,0,0,0,0 from Issue_Details Where Issue_No IN(Select Issue_No from Issue_Hdr Where Issue_Date > Convert(varchar,@maxDt,101) and Issue_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Goods receipt*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, Sum(Qty) as Purchase,0,0,0,0,0 from Dlv_note_Details Where Dlv_Note_No IN(Select Dlv_Note_No from Dlv_Hdr Where Dlv_Note_Date > Convert(varchar,@maxDt,101) and Dlv_Note_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Rejection after receipt*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,Sum(Qty) as Rejected,0,0,0,0 from Rejection_Details Where Rejection_No IN (Select Rejection_No from Rejection_Hdr Where Rejection_Date > Convert(varchar,@maxDt,101) and Rejection_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Issues returns*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,Sum(Qty) As Issue_Returns,0,0,0 from Issue_Return_Details Where Issue_R_No IN(Select Issue_R_No from Issue_Return_Hdr Where Return_Date > Convert(varchar,@maxDt,101) and Return_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Physical Stock + */ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,0,Sum(Var_Qty) as Phy_Qty_P,0,0 from Physical_Details Where Var_Qty>0 and Txn_No IN(Select txn_No from Physical_Hdr Where Txn_Date > Convert(varchar,@maxDt,101) and Txn_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Physical -*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,0,0,Sum(Var_Qty) as Phy_Qty_M,0 from Physical_Details Where Var_Qty<0 and Txn_No IN(Select txn_No from Physical_Hdr Where Txn_Date > Convert(varchar,@maxDt,101) and Txn_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* insert all the records into actual table i,e Stock_dump from Stock_dump_temp (temporory table)*/ INSERT INTO Stock_Dump Select Product_code,Txn_Date, Sum(Opening_Stock) as Opening_Stock,Sum(Issue_Qty) as Issue_Qty,Sum(purchase) as Purchase,Sum(Rejected) as Rejected,Sum(Issue_Returns) as Issue_returns, Sum(Phy_Qty_P) as Phy_Qty_P,Sum(Phy_Qty_M) as Phy_Qty_M,0 as Closing_Stock from Stock_Dump_Temp Group By ProducT_Code,Txn_Date /* update closing stock*/ UPDATE Stock_Dump Set Closing_Stock=abs((Opening_Stock+Purchase+Issue_Returns+Phy_Qty_P)-(Issue_Qty+Rejected+Phy_Qty_M)) Where Txn_Date=Convert(varchar,Getdate(),101) /* delete unwanted records */ DELETE From Stock_Dump Where Opening_Stock=0 and Issue_Qty=0 and Purchase=0 and Rejected=0 and Issue_Returns=0 and Phy_Qty_M=0 and Phy_Qty_P=0
set @strReturn='Stock Table Update Successfully' return END
END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I have a database which contains more than 20000 stored procedureswhich were created withansi nulls off. This i found out using the querySELECT name,AnsiNullsOn FROM(SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOnFROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0Is there any way that i can set this property to 1 for all the storedprocedures i have??I know the alternate method is to drop the procedure and execute thescripts again with AnsiNullsOn = 1.Is there any other simple ways?? It will be very helpful for me..
Now I have lets say VISID 1 to 50. I'm using this SP to change the text on a button. Now I have 50 buttons. So I run this SP, then I run this in my vb.net code
Code Snippet Dim constr As New SqlConnection(PVDBConn) Try 'Variable to hold the results Dim results As String = String.Empty cmdUpd = New SqlCommand("SelVis1Name", constr) cmdUpd.CommandType = CommandType.StoredProcedure constr.Open() 'Set results to the value returned from ExecuteScalar() results = CType(cmdUpd.ExecuteScalar(), String) constr.Close() 'Set our buttons text to that value Button1.Text = results Catch ex As Exception MsgBox(ex.Message.ToString) End Try
At any time, when I start my program, I may need to label 10 buttons, or up to 50. Now I will have this number in a text file. Can I grab that number from a text file, and pass it into a SP?
And can I write this SP only once, to work for more than one label per time. Or do I have to write this sp 50 times?
I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.
I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?
I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.
I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.
I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?
Hi all, I m new to this forum and this is my first question. I m having 2 pages in my web site ... page 1 query directly to db using sqldatasource, the second page query through a BLL then DAL by following the step in this tutorial (http://www.asp.net/learn/dataaccess/tutorial02vb.aspx?tabid=63).... Page 1 is using a "Like" query search and the Page 2 is the normal displaying some product detail.... Under normal circumstances, one will expect Page 1 will be way fastest than the Page 2... however the problem is Page 1 is in thunder speed while Page 2 takes 10 secs to load... 10 seconds is really not acceptable... I really couldnt figure out what happens... both Page 1 and Page 2 are using the same connection string which connection through a DSN.... How is the connection different by using sqldatasource and DAL?? Could someone please help.... ThanksP.S. I m using a Pervasive database
Hi y'all, I've recently run a profiler on my code and following query took 7 seconds: SELECT TOP 10 UI, COUNT(UI) AS Expr1 FROM table WHERE (UI <> 'custom_welcome') GROUP BY UI ORDER BY COUNT(UI) DESC Is it possible to rewrite this so my code gets faster? It's also possible that it's due to the size of the table? Thanks in advance! I'll let you know how long your query takes :)
I have a cursor prcedure that is pretty slow because as the cursor moves through the data I have three select statement on the same table to find other rows information. Is there a better way to do this? Simple Example of Code is: DECLARE MyVARABLES 1 to X DECLARE c1 CURSOR FOR SELECT MyData1, MyData2 to X FROM MyTable FOR UPDATE OF MyUpdateData --Start Cursor OPEN c1 FETCH NEXT FROM c1 INTO MyVariables --LOOP WHILE @@FETCH_STATUS = 0 BEGIN ----------------------- -- Get other rows data to add to this rows data ......GUESSING THIS IS THE SLOW PART as the table is LARGESELECT MyVar1 = MyData1 FROM MyTable WHERE MyTableColumns = MyVariables AND MyTableColumns2 <> MyVariables2 --FINDS OTHER ROW (I have three of these)
--Calculate & Update If MyVarable = 'this or that' BEGIN UPDATE MyTable SET MyUpdateData = MyVar1 * x *y WHERE CURRENT OF c1 END ------------------- -- NextFETCH NEXT FROM c1 INTO MyVarables 1 to xEND CLOSE c1 DEALLOCATE c1
Hi! I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips?????
Question 1: In a stored procedure, One SELECT stmt is there & depending upon the @rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way???
Question 2:Can anybody give me few examples like this?????? I need them desparetly.
I´ve created a class to make some standard transaction development a little bit faster. The destructor seem to run, but something makes this object slow down the database, if SqlTransaction and/or SqlConnection isnt manualy handled with the method Commit(). Any ideas on how to handle the SqlTransaction and SqlConnection better?
public class DataTransaction { private bool blnError = false; private ArrayList arrErrorList = new ArrayList(); private SqlConnectionobjConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString()); private SqlTransactionobjTransaction;
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLE job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs. And I am talking about major improvement in terms of completion of the tasks here :)
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I have rewritten a stored procedure that consists of a single select that selects from a view. Essentially I combined the select in the view and the select in the sp into one select. I am now trying to determine if the new version is faster.
The estimated execution plan gives a ratio of 96% : 4% in favour of the new version when I run them together from a query window but when I try to time them I can't get a satisfactory result.
If I run each query once and display the difference between start and end time, they display 0. If I run each one 100, 200, etc times I get different results each time.
If I had a WHERE clause that had to compare a string to another string would it be faster one way or another if I broke it down to three different, smaller searches?
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I have a live database and an archive database. I update the archive tables once a day from the live tables using:
INSERT INTO arc_table SELECT * FROM cur_table AS cur WHERE NOT EXISTS (SELECT * FROM arc_table AS arc WHERE arc.key = cur.key)
GO
This inserts newer records into the archive tables from the live tables.
I have two different methods to clean the live tables once a week but keep data from the previous week. Both methods have been verified to delete the same rows.
DELETE cur_table WHERE EXISTS (SELECT key FROM arc_table AS arc WHERE arc.key = cur_table.key) AND date_time < GetDate() - 7
GO
Second method modified from BOL - deletes identical rows
DELETE cur_table FROM (SELECT key FROM arc_table) AS arc WHERE arc.key = cur_table.key AND date_time < GetDate() - 7
GO
I read that "WHERE [NOT] EXISTS" is faster than "WHERE [NOT] IN" but this is the first time I have seen DELETE xx FROM (SELECT ----)
I'd like to know which procedure will be faster and/or better.
HelloI need this really faster in mS SQL 2000Usernumber (int)reportid (FK)reportreportid (PK)Category (int)SELECT A, B, C, D INTO UserCopy FROM UserWHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Categoryand the table user has a few millions rowsReport.ReportId is a Primary key for User.reportid (FK) for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximumthank you for helping
