What's wrong and How can this problem be optimized ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

What's wrong and How can this problem be optimized ?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-18 : 09:48:19

Gnanendra writes "Hello,
I am a VC programmer doing a performance compression on the database, which is run periodically(every 1,2,4...weeks, based on the selection by the user). Naturally, I know very little about sql queries and performance tuning ;-)

Let me brief with my scenario.
I have a table "table1" where the fields are:
t1id - int - primary
machineid - int - primary
fromdate - datetime
todate - datetime ...
locationid - int
zoneid - int
And I have a relational table "table2" where for every entry in table1, I have around 100 records in table2.
This table has the fields:
t2id - int - primary
t1id - int - foreign
detail1 - int
detail2 - int
Apart from the auto indexing of primary keys, I am indexing on,
table1 - Fromdate, locationid :Clustered
table2 - t1id :NonClustered <Clustering increases the query time>

Now the task is to compress these records based on weeks.
the loop is as follows;
---for every zone
------for every location
---------compress the records based on the target(let's assume 1 week)

so, I query records for every 1 wk - starting from this week's beginning - using the select query into a recordset, then move thru every record (plus the 100 records of table2), perform some algorithmic calculations, and finally remove most of the records(not all) within the recordset (identified by the pr key) and finally replace by 1 record (plus 100 table2 records).

The select query is simple:
select fields from table1, table2 where table1.fromdate>="the week's beginning"(changeable) and table1.todate<="the week's end"(changeable) and table1.t1id=table2.t1id and table1.zoneid=1(changeable) and table1.locationid=1(changeable) order by table1.fromdate, table2.detail1

The above query goes on till the query doesn't return a recordset (I store the end date of the loop). Then the loop moves to the next zone and location, and again from the current week and so on...
For example:
let's assume that i have a total record count of 144000 table1 records, and hence i have corresponding 144000*100 = 14400000 table2 records. The db size is around 1GB.For every week, in a simple sense, I get a recordset of 7days*24hrs=168 table1 and 16800 table2 records.So, I have to query these records, do the calcs, and remove the 16800+168 records from the two tables, insert a new record.
This happens for evey week, till I move out of the recordset(EOF).

The traces I have obtained is as follows:
--- Beginning ---
13 Apr 2002 @ 18:06:01 ==> Select FROM 2002/01/28 00:00:00 TO 2002/02/04 00:00:00 FOR Zone=1 and Loc=10013 Apr 2002 @ 18:06:01 ==> Select - Start
13 Apr 2002 @ 18:06:13 ==> Select - End -- 12 secs
13 Apr 2002 @ 18:06:16 ==> Remove 168 table1 + 16800 table2 - Start
13 Apr 2002 @ 18:06:42 ==> Remove 168 table1 + 16800 table2 - End -- 26 secs
13 Apr 2002 @ 18:06:42 ==> Insert 1 table1 record - Start
13 Apr 2002 @ 18:06:42 ==> Insert 1 table1 record - End -- less than 1 sec
13 Apr 2002 @ 18:06:42 ==> Insert 100 table2 records - Start
13 Apr 2002 @ 18:06:43 ==> Insert 100 table2 records - End -- around 1 sec
This is quite consistent till it reaches the end of the query cycle.
-- The trace at the end is:
14 Apr 2002 @ 08:32:03 ==> Select FROM 2002/02/04 00:00:00 TO 2002/02/11 00:00:00 FOR Zone=1 and Loc=1
14 Apr 2002 @ 08:32:03 ==> Select - Start
14 Apr 2002 @ 08:54:45 ==> Select - End -- ## 23 mins (1380 secs) ##
14 Apr 2002 @ 08:54:48 ==> Remove 168 table1, 16800 table2 - Start
14 Apr 2002 @ 08:55:31 ==> Remove 168 table1, 16800 table2 - End -- 43 secs
14 Apr 2002 @ 08:55:31 ==> Insert 1 table1 record - Start
14 Apr 2002 @ 08:55:33 ==> Insert 1 table1 record - End -- around 2 secs
14 Apr 2002 @ 08:55:33 ==> Insert 100 table2 records - Start
14 Apr 2002 @ 08:55:34 ==> Insert 100 table2 records - End -- 1 sec

Additional Info:
I am using stored procedures f

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-04-18 : 09:48:19

And we truncated at 4,000 characters.

Gnanendra
Starting Member

1 Post

Posted - 2002-04-19 : 00:06:23

Additional Info:
I am using stored procedures for removal and insertion of records. Also I have used Clustered indexing on the fields: Fromdate, Locationid in that order.

And for friends who say that I could do away with the recordset, I have to say that I definitely need records in the recordset. The whole algo lies in moving thru the recordset.
One important query:
I have a feeling that using stored procedure to get the recordset pointer using the query condition can improve the query timings. So, can I get a recordset pointer from a stored procedure? If so, How?
The input parameters should be the "where" condition params. BUT my above abnormal condition still stands and has to be answered ;-)

Why is this unusual consumption of time in the select query? I am really stumped by this behaviour. Awaiting speedy solutions/suggestions to this predicament.

Thanx in advance,
Gnanendra.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources