Showing posts with label additional. Show all posts
Showing posts with label additional. Show all posts

Monday, March 26, 2012

Optimize this query?

I have a nightly job that aggregates data into a summary table. Each night, an additional item may or may not be added, so the table is cleared and repopulated. I've got this working fine. Now, I am being asked to track when an item first appears in the summary table. Here is the code I am using to do this. I realize it is very poor performance, but I don't know how to make it better.

MyTable Columns:
id INT PK
,v1 VARCHAR(4)
,v2 VARCHAR(10
,v3 DECIMAL
,item1 BIT
,item2 BIT
,item3 BIT

MyTable2 Columns:
v1 VARCHAR(4)
,v2 VARCHAR(10
,v3 DECIMAL
,item INT
,FirstKnownDate DATETIME

All columns are NOT NULL.

1DECLARE @.iNextRowIDINT2 ,@.iCurrentRowIDINT3 ,@.iLoopControlINT4 ,@.v1VARCHAR(4)5 ,@.v2VARCHAR(10)6 ,@.v3DECIMAL78/* This is a loop that executes for a dynamic number of items, eg item1=1, then item2=1, etc */9SET @.iLoopControl=110SELECT @.iNextRowID=MIN(id)FROM MyTableWHERE item1=11112IFISNULL(@.iNextRowID,0) <> 0BEGIN13 SELECT @.iCurrentRowID = id14 ,@.v1 = v115 ,@.v2 = v216 ,@.v3 = v317FROM MyTable18WHERE id=@.iNextRowID1920WHILE @.iLoopControl = 1BEGIN21 IFNOT EXISTS(SELECT *FROM MyTable2WHERE v1=@.v2AND v2=@.v2AND v3=@.v3AND item=1)BEGIN22 INSERT INTO MyTable223/* COLUMN */ (v1,v2,v3,item,firstknowndate)24SELECT @.v1, @.v2, @.v3, 1,GETDATE()25END2627 SELECT @.iNextRowID =NULL28SELECT @.iNextRowID =MIN(id)FROM MyTableWHERE id>@.iCurrentRowIDAND item1=12930IFISNULL(@.iNextRowID,0) = 0BEGIN31 BREAK32 END3334 SELECT @.iCurrentRowID = id35 ,@.v1 = v136 ,@.v2 = v237 ,@.v3 = v338FROM MyTable39WHERE id=@.iNextRowID40END41END
42/* This is the end of the items loop */
This query takes 5 minutes to do straight inserts without the test for exists in the loop for 3 items.  MyTable has just under 96,000 rows in it. MyTable2 has 121,857 rows in it. It is not about 55 minutes and it is still running with the exists so that a row isn't inserted twice. Is there anything I can do to optimize this?
 EDIT: The query just finished running again with the test for exists on each row. It shouldn't have inserted any new rows, but it took 57 minutes and inserted another 114,115 rows. Not good. Anyone see what is wrong with the query?

Have a look at the execution plan and see what costs are associated with it.

|||

This should be obvious just by looking at the query. There are so many select statements, at least 125,000 exists statements. Obviously it won't finish in a few seconds. I just mean, is there a better way to loop through each row and insert or do nothing based on whether that row exists in the second table?

|||

I just meant that it would show you exactly where the bottlenecks exist. As for improving the query, you could probably just join to the second table and perform inserts for all the missing rows.

|||

Code sample? Eliminating the exists is really what is needed.

Wednesday, March 7, 2012

Operational but able to restore additional transaction logs

I have databases which have been left in the operational, but able to restore
additional transaction logs mode. I had done this last week thinking I could
easily setup log shipping that day. Tonight's backup will be the first full
backup corresponding with transaction log backups. The full backup will need
to run against this database which currently shows (loading) as its state.
Should this be removed or fixed somehow - at this point, I cannot delete, or
restore, or anything else including go to the properties to reset the state
because the database is not in the databases collection for this server...
so, what do I do now?
Regards,
Jamie
Wow, I figured that one out. I checked the sysdatabases as I had tried to
detach the database that was indicated as loading. Detaching removes it
from the sysdatabases. I only needed to refresh the Enterprise Manager.
Regards,
Jamie
"thejamie" wrote:

> I have databases which have been left in the operational, but able to restore
> additional transaction logs mode. I had done this last week thinking I could
> easily setup log shipping that day. Tonight's backup will be the first full
> backup corresponding with transaction log backups. The full backup will need
> to run against this database which currently shows (loading) as its state.
> Should this be removed or fixed somehow - at this point, I cannot delete, or
> restore, or anything else including go to the properties to reset the state
> because the database is not in the databases collection for this server...
> so, what do I do now?
> --
> Regards,
> Jamie