Prev: INSERT statement conflicted with COLUMN CHECK constraint.
Next: LEFT JOIN and unnecessary reads
From: tshad on 7 Apr 2010 17:31
I have a procedure that does the same set of 3 Update Queries 4 times. The
only difference in the 3 queries is whether the status is 1, 2 or 3.

So I can combine each of the 3 into 1 query (Where Status Between 1 and 3)
and end up with 4 queries.

These 4 are essentially the same except one of the JOINS is a different
table or view.

I was thinking of combining them into one dynamic expression and having only
one select statement where the INNER JOIN changes depending which of the 4
loops I am doing.

In each case, I am still doing 4 statements.

I assume the 4 statements would be better then 4 dynamic sql calls?

I was thinking in the dynamic sql it would have to recompile each time.

Thanks,

Tom


From: Plamen Ratchev on 7 Apr 2010 18:40
I am not sure I understand how the static statements differ from the dynamic SQL calls. Maybe post the SQL code.

If you use sp_executesql with parameter the query plan is cached and can be reused. And recompilation is not necessarily
a bad thing. In some cases the cost of recompile is insignificant compared to using cached inefficient plan.

See Erland's article on dynamic search:
http://www.sommarskog.se/dyn-search-2005.html

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 7 Apr 2010 19:03

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:n9CdnSSU3aRqlCDWnZ2dnUVZ_sWqnZ2d(a)speakeasy.net...
>I am not sure I understand how the static statements differ from the
>dynamic SQL calls. Maybe post the SQL code.
>

I could either do something like this where the INNER JOINS changes to
Table1, Table2, Table3 in 4 separate queries.

UPDATE TableA
SET TableA.Description = B.Description
FROM ##TableGT B
INNER JOIN vwCustomer vw
ON vwAView.ExternalID = B.ID AND
vw.CatID = B.ID
WHERE B.session = @session AND
B.batchID = @batchTimeStamp AND
B.ID BETWEEN @StartRow AND @EndRow

Or change it to something like:

SET @Ktr = 1
WHILE @Ktr <5

SELECT @Sql = 'UPDATE TableA
SET TableA.Description = B.Description
FROM ##TableGT B
INNER JOIN ' + CASE WHEN @Ktr = 1 THEN
'vwCustomer'
ELSE
@Ktr = 2 THEN 'Table1'
ELSE
@Ktr = 3 THEN 'Table2'
ELSE
@Ktr = 4 THEN 'Table3' END + ' vw
ON vwAView.ExternalID = B.ID AND
vw.CatID = B.ID
WHERE B.session = @session AND
B.batchID = @batchTimeStamp
AND
B.ID BETWEEN @StartRow AND
@EndRow'

EXECUTE sp_executesql @Sql,...

SET @Ktr = @Ktr + 1

BEGIN
END

Thanks,

Tom
> If you use sp_executesql with parameter the query plan is cached and can
> be reused. And recompilation is not necessarily a bad thing. In some cases
> the cost of recompile is insignificant compared to using cached
> inefficient plan.
>
> See Erland's article on dynamic search:
> http://www.sommarskog.se/dyn-search-2005.html
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 7 Apr 2010 19:13
For this may be better to go with dynamic SQL. You can try using IF conditional flow to execute different queries, or
even create separate stored procedures for each case. Again, in Erland's article you will find good discussion on the
different approaches to handle this.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 7 Apr 2010 20:23

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:n9CdnSeU3aQ8jCDWnZ2dnUVZ_sU3AAAA(a)speakeasy.net...
> For this may be better to go with dynamic SQL. You can try using IF
> conditional flow to execute different queries, or even create separate
> stored procedures for each case. Again, in Erland's article you will find
> good discussion on the different approaches to handle this.
>
OK.

I'll take a look at it.

What about the CASE statement, wouldn't that work?

I figured the dynamic SQL would be better as well because if you make
changes you have to make them in 4 places. I was mainly concerned with the
performance since they were paging to prevent timeouts ( which I think
should be preventable in any case).

Thanks,

Tom

> --
> Plamen Ratchev
> http://www.SQLStudio.com


 |  Next  |  Last
Pages: 1 2
Prev: INSERT statement conflicted with COLUMN CHECK constraint.
Next: LEFT JOIN and unnecessary reads