Friday, March 30, 2012
Optimizing SQL Query
I have to run a script which will delete records from 10 tables
based on a certain condition. Instead of having to run the condition 10
times I thought running it once would have better performance and did
something like this
CREATE PROCEDURE testScript AS
set nocount on
declare @.script_table table (row_id int identity(1,1), sid int)
declare @.max int, @.now int, @.result varchar(100)
insert into @.script_table
select sid from Alpha where lname like 'DOWN' and (fname like 'Tom' or
fname like 'Seinfeld')
select @.max = max(row_id) from @.script_table
select @.now=2, @.result=convert(varchar(6), sid) from @.script_table
where row_id=1
while @.max >=@.now
Begin
select @.result = @.result + ',' + convert(varchar(6), sid) from
@.script_table where row_id=@.now
set @.now=@.now + 1;
End
select * from Beta where convert(varchar(5), sid) in ( @.result )
if @.@.error <> 0
BEGIN
ROLLBACK Transaction
RAISERROR ( 'E', 2, 127)
END
...
...
...
but when I run this I dont get any values. Instead when I run this
query I get the output rows
select * from Beta where convert(varchar(5), sid) in (select sid from
Alpha where lname like 'DOWN' and (fname like 'Tom' or fname like
'Seinfeld'))
since @.result has the output from Alpha with a comma delimiter I was
under the impression that this should give me the result but instead I
dont get any rows. Is this because @.result is a varchar? Am I doing
something wrong here? Any help would be great..
Thanks
Khris> Is this because @.result is a varchar?
Yes. You can, however, parse a delimited string and have values returned in
a table, that you can use in your query.
My favourite solution is this one (by Dejan Sarka):
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks ML, I will try that out|||Thanks ML I will try that out
Khris
Wednesday, March 28, 2012
Optimizing datediff
I run a monthly delete that's recurring; delete data > 2 months old. For December, I thought up two ways to do this:
Query 1:
delete from daily_statements where daily_statements < '2006-10-01'
Query 2:
delete from daily_statements where datediff(month, statement_date, getdate()) <= 3
I've plugged in Query 2 into the job step because it can be run at any time without having to change the value, but it took 22 minutes to delete 2.5 million rows versus 4 minutes to delete 5.3 million rows for the other one.
Is query 2 the best it can be? Thanks!
If the statement_date column is indexed, the 2nd query search argument is not efficient since the use of the DATEDIFF function on the column will eliminate the use of the index. The index contains the actual date values so the query optimizer needs search arguments that doesn't change the column in any way. You can do something like below instead:
delete from daily_statements
where statement_date < dateadd(month, -2, convert(varchar, dateadd(day, 1-day(current_timestamp), current_timestamp), 112))
The expression using CURRENT_TIMESTAMP gives the start of the current month and then goes past 2 months. You can then use it to generate the value needed for the statement_date column comparison. In general, expression involving the column directly is more efficient than expressions that call functions or user-defined functions on columns.
|||Use a variable to compare the dates:DECLARE @.enddate datetime
SET @.enddate = DATEADD(month, -2, GETDATE())
SET @.enddate = CAST(CAST(MONTH(@.enddate) AS VARCHAR(2)) + '/01/' + CAST(YEAR(@.enddate) AS VARCHAR(4)) AS DATETIME)
delete from daily_statements where daily_statements < @.enddate
|||Please don't use the local variable approach. It does not allow the optimizer to do parameter sniffing for example. And since the batch is optimized as a whole an estimate will be used for the variable and it will result in sub-optimal plan. Using the CURRENT_TIMESTAMP expression inline provides the best performance since the query optimizer has built-in rules for such expressions. Also, using a string format for datetime/smalldatetime that is dependent on regional settings or language of the session can result in run-time errors.|||It should treat the local var like a "static" like if I say startdate < '10/1/2006'.
You are correct about the date format. I used the US date, because that was easy.
optimizing a query to delete duplicates
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:
+----+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 123 |
+--+--+
As you can see, the data is tabular. This is how it is stored in the table:
+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 234 |
+--+----+----+
What I need is to delete all records having the same "Row" when there exists
the same set of records with a different (smaller, to be precise) "Row".
Using the example above, what I need to get is:
+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
+--+----+----+
A slow way of doing this seem to be:
DELETE FROM X
WHERE Row IN
(SELECT DISTINCT Row FROM X x1
WHERE EXISTS
(SELECT * FROM X x2
WHERE x2.Row < x1.Row
AND NOT EXISTS
(SELECT * FROM X x3
WHERE x3.Row = x2.Row
AND x3.FieldName = x2.FieldName
AND x3.FieldValue <> x1.FieldValue)))
Can this be done faster, better, and cheaper?my knee-jerk reaction is:
Why is it important to optimize it? I think you should delete the
duplicates, then create a constraint that prevents them from recurring.
If, for some reason, you are unable to fix the application that creates
these duplicates, and creating a constraint causes errors in the application
that you can't tolerate, then I suppose an alternative would be to create a
trigger that deletes them upon entry. Having a composite index on the
columns that are being duplicated would enable such a trigger to run
quickly.
But looking at your query, I find it strangely complex.
Why not just:
DELETE FROM X
WHERE EXISTS (SELECT * FROM X x2
WHERE x2.Row < x.Row
AND X.FieldName = x2.FieldName
AND X.FieldValue = x2.FieldValue)
Am I missing something? Your NOT EXISTS has me a bit confused... I think it
might delete data in situations other than described.
Also, NOT EXISTS is generally slow.|||On 2004-07-15, Aaron W. West <tallpeak@.hotmail.NO.SPAM> wrote:
> Why is it important to optimize it? I think you should delete the
> duplicates, then create a constraint that prevents them from recurring.
Such constraint may not be created. This table is a temporary table, where
data from an input file is loaded. Duplicate sets of records must be
deleted because the data then goes into permanent tables. Those table have
constraints against duplicates.
> But looking at your query, I find it strangely complex.
Me too. I'm trying to improve it. Its complexity seems to hinder its
performance.
> Why not just:
> DELETE FROM X
> WHERE EXISTS (SELECT * FROM X x2
> WHERE x2.Row < x.Row
> AND X.FieldName = x2.FieldName
> AND X.FieldValue = x2.FieldValue)
This would delete records that should not be deleted. Here's an example:
+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 666 |
+--+----+----+
Here the combination of values for "a" and "b" on every "Row" is
different. There are no duplicates here. The query that you proposed would
delete the second to last row
+--+----+----+
| 3 | a | 123 |
+--+----+----+
because it has the same FieldName and FieldValue as the first row.
Think of it the data this way:
+--+--+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 666 |
+--+--+
No duplicate rows here.|||Hi
You could try only selecting the correct data when you move it into the
permanent tables. But the following may work better:
DELETE FROM X1
FROM X X1 JOIN X X2
ON x2.Row < x1.Row
AND x1.Fieldvalue = x2.Fieldvalue
AND x1.FieldName = x2.FieldName
John
"Alexander Anderson" <no@.spam.com> wrote in message
news:slrncfe0ft.mk1.alex@.Toronto-HSE-ppp3682122.sympatico.ca...
> I have a DELETE statement that deletes duplicate data from a table. It
> takes a long time to execute, so I thought I'd seek advice here. The
> structure of the table is little funny. The following is NOT the table,
> but the representation of the data in the table:
> +----+
> | a | b |
> +--+--+
> | 123 | 234 |
> | 345 | 456 |
> | 123 | 123 |
> +--+--+
> As you can see, the data is tabular. This is how it is stored in the
table:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> | 3 | a | 123 |
> | 3 | b | 234 |
> +--+----+----+
> What I need is to delete all records having the same "Row" when there
exists
> the same set of records with a different (smaller, to be precise) "Row".
> Using the example above, what I need to get is:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> +--+----+----+
> A slow way of doing this seem to be:
> DELETE FROM X
> WHERE Row IN
> (SELECT DISTINCT Row FROM X x1
> WHERE EXISTS
> (SELECT * FROM X x2
> WHERE x2.Row < x1.Row
> AND NOT EXISTS
> (SELECT * FROM X x3
> WHERE x3.Row = x2.Row
> AND x3.FieldName = x2.FieldName
> AND x3.FieldValue <> x1.FieldValue)))
> Can this be done faster, better, and cheaper?
Friday, March 9, 2012
Opinons on using Delete then BULK INSERT
INSERT. It reads through the flat file first deleting rows in the target
table that if finds. It then issues the BULK INSERT command on the same flat
file. This has the effect of doing an insert or update.
The DBA reports that the tables tend to become more fragmented because of
the deletes rather than using update.
Are there any pitfalls to this approach? Does it sound OK?
Are there other approaches other than Transact SQL for inserting or updating
large amounts of flat file data?
Thanks!!
McG
[url]http://mcg
news:eudcIdGPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi. Just looking for your opinions on a technique I have seen for using
> BULK INSERT. It reads through the flat file first deleting rows in the
> target table that if finds. It then issues the BULK INSERT command on the
> same flat file. This has the effect of doing an insert or update.
> The DBA reports that the tables tend to become more fragmented because of
> the deletes rather than using update.
> Are there any pitfalls to this approach? Does it sound OK?
> Are there other approaches other than Transact SQL for inserting or
> updating large amounts of flat file data?
> Thanks!!
> --
> McG
> [url]http://mcg
>
>
It depends on how big the tables are and how big the flat file is.
Personally, I would probably BULK INSERT the flat file into a working table
first. Then using a chunking method, I would update/insert about 10,000
rows at a time until the task completed. This has the advantage of letting
SQL Server do what it does best using SET theory for determining updates and
inserts, but also doing it in small enough chunks that other processing can
continue with only slight interruption.
Just my .02
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the pointer Rick.
McG
[url]http://mcg
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%23pxvSgGPGHA.1124@.TK2MSFTNGP10.phx.gbl...
> "McG
> news:eudcIdGPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> It depends on how big the tables are and how big the flat file is.
> Personally, I would probably BULK INSERT the flat file into a working
> table first. Then using a chunking method, I would update/insert about
> 10,000 rows at a time until the task completed. This has the advantage of
> letting SQL Server do what it does best using SET theory for determining
> updates and inserts, but also doing it in small enough chunks that other
> processing can continue with only slight interruption.
> Just my .02
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>