Showing posts with label nightly. Show all posts
Showing posts with label nightly. 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 21, 2012

Optimizations

I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew

Optimizations

I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
Drew
Drew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
sql

Optimizations

I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of th
e
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless th
e
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail
.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew

Optimization Job fails

Hi everyone,
We have more than 100 databases on one SQL Server instance and all databases
are backed up and optimised in the nightly maintenance plan.
I copied a new database in this instance that has some computed columns in
few tables. Now, I'm getting this error in daily maintenance plan's
optimization job that it failed because "Quoted_Identifier" is OFF.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Any idea/help to overcome this situation. I have some other databases with
computed columns and have same set of database options. There is no problem
with those databases. Only one database is causing this. Any idea ?
Thanks in advance.Sqlmaint.exe does not set quoted_identified on by default. You should create
a T-SQL job which sets this option. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;301292 for a similar
problem.
Adrian
"Ahmed" <Ahmed@.discussions.microsoft.com> wrote in message
news:1E48F884-3DBD-465E-BDB9-0D0B110BA832@.microsoft.com...
> Hi everyone,
> We have more than 100 databases on one SQL Server instance and all
> databases
> are backed up and optimised in the nightly maintenance plan.
> I copied a new database in this instance that has some computed columns in
> few tables. Now, I'm getting this error in daily maintenance plan's
> optimization job that it failed because "Quoted_Identifier" is OFF.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the following
> SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Any idea/help to overcome this situation. I have some other databases with
> computed columns and have same set of database options. There is no
> problem
> with those databases. Only one database is causing this. Any idea ?
> Thanks in advance.
>

Optimization Job fails

Hi everyone,
We have more than 100 databases on one SQL Server instance and all databases
are backed up and optimised in the nightly maintenance plan.
I copied a new database in this instance that has some computed columns in
few tables. Now, I'm getting this error in daily maintenance plan's
optimization job that it failed because "Quoted_Identifier" is OFF.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Any idea/help to overcome this situation. I have some other databases with
computed columns and have same set of database options. There is no problem
with those databases. Only one database is causing this. Any idea ?
Thanks in advance.
Sqlmaint.exe does not set quoted_identified on by default. You should create
a T-SQL job which sets this option. See
http://support.microsoft.com/default...b;en-us;301292 for a similar
problem.
Adrian
"Ahmed" <Ahmed@.discussions.microsoft.com> wrote in message
news:1E48F884-3DBD-465E-BDB9-0D0B110BA832@.microsoft.com...
> Hi everyone,
> We have more than 100 databases on one SQL Server instance and all
> databases
> are backed up and optimised in the nightly maintenance plan.
> I copied a new database in this instance that has some computed columns in
> few tables. Now, I'm getting this error in daily maintenance plan's
> optimization job that it failed because "Quoted_Identifier" is OFF.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the following
> SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Any idea/help to overcome this situation. I have some other databases with
> computed columns and have same set of database options. There is no
> problem
> with those databases. Only one database is causing this. Any idea ?
> Thanks in advance.
>

Optimization Job fails

Hi everyone,
We have more than 100 databases on one SQL Server instance and all databases
are backed up and optimised in the nightly maintenance plan.
I copied a new database in this instance that has some computed columns in
few tables. Now, I'm getting this error in daily maintenance plan's
optimization job that it failed because "Quoted_Identifier" is OFF.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following
SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Any idea/help to overcome this situation. I have some other databases with
computed columns and have same set of database options. There is no problem
with those databases. Only one database is causing this. Any idea ?
Thanks in advance.Sqlmaint.exe does not set quoted_identified on by default. You should create
a T-SQL job which sets this option. See
http://support.microsoft.com/defaul...kb;en-us;301292 for a similar
problem.
Adrian
"Ahmed" <Ahmed@.discussions.microsoft.com> wrote in message
news:1E48F884-3DBD-465E-BDB9-0D0B110BA832@.microsoft.com...
> Hi everyone,
> We have more than 100 databases on one SQL Server instance and all
> databases
> are backed up and optimised in the nightly maintenance plan.
> I copied a new database in this instance that has some computed columns in
> few tables. Now, I'm getting this error in daily maintenance plan's
> optimization job that it failed because "Quoted_Identifier" is OFF.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the followi
ng
> SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Any idea/help to overcome this situation. I have some other databases with
> computed columns and have same set of database options. There is no
> problem
> with those databases. Only one database is causing this. Any idea ?
> Thanks in advance.
>