Dynamic query problem - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Dynamic query problem

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-21 : 06:44:21

I have the following dynamic queries which is taking enormous amount of time and sometimes timing out depending on the data. How do i get rid of the dynamic query? Also what is the alternative to the cursor?
Thank you for your help.

--get the count
SET @sql = N'
SELECT @cnt = COUNT(*) FROM
(
SELECT DISTINCT tblD.Name cnt
FROM tblES INNER JOIN
tblE
ON tblES.tblEId = tblE.tblEId INNER JOIN
tblA ON tblE.EID = tblA.Id INNER JOIN
tblD ON tblA.DID = tblD.Id
INNER JOIN tblL On tblA.Id = tblL.Id
WHERE (tblA.OID = '+ CAST(@OID as VARCHAR) +')
AND (tblE.Year = '+ CAST(@yearEnding as VARCHAR) + ')'

IF (@EID IS NOT NULL)
BEGIN
SET @sql = @sql + ' AND (tblE.EID IN ('+@EID+'))'
END
SET @sql = @sql + ' GROUP BY tblA.DID,tblE.OS,tblD.Name) X '

SET @ParmDefinition = N'@cnt int OUTPUT'

EXECUTE sp_executesql @SQL, @ParmDefinition, @cnt=@cnts OUTPUT

--now run the main query taking the count from the above query
SET @query = N'
SELECT * FROM
(SELECT DISTINCT tblA.DID, tblD.Name,
ISNULL(AVG(tblE.OS), 0) AS OS,
CONVERT(DECIMAL(5,2),(CONVERT(DECIMAL,ROW_NUMBER() OVER (ORDER BY AVG(OS) ASC),1)/' + CAST(@cnts AS VARCHAR) + ') * 100) Percent,
CONVERT(INT,DENSE_RANK() OVER (ORDER BY AVG(OS) DESC)) Ranks
FROM tblES INNER JOIN
tblE
ON tblES.tblEId = tblE.tblEId INNER JOIN
tblA ON tblE.EID = tblA.Id INNER JOIN
tblD ON tblA.DID = tblD.Id
INNER JOIN tblL On tblA.Id = tblL.Id
WHERE (tblA.OID = '+ CAST(@OID as VARCHAR) +')
AND ((tblE.Year = '+ CAST(@yearEnding as VARCHAR) + '))'
IF (@EID IS NOT NULL)
BEGIN
SET @query = @query + ' AND (tblE.EID IN ('+@EID+'))'
END
SET @query = @query + '
GROUP BY tblA.DID, tblD.Name
) DRV(DID, DID,Name,OS,Percent,Ranks)
LEFT JOIN tblS s on ROUND(OS,1) >= s.Min
and ROUND(OS,1) <= s.Max
order by Percent DESC'
EXEC (@query)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-21 : 07:01:23

Is it possible to explain the problem that is solved by the given code in your post or do you want us to construct the problem while inspecting the code?

If it is possible to explain the problem then please give table structure, sample data and wanted result.

Too old to Rock'n'Roll too young to die.

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-21 : 07:31:10

In this case you do not need a table structure. Look at the queries. First query is getting count of records and the second one is using the count to get the percentage. The reason why I am using dynamic query is to concatenate the sql based on a condition. What do i need to do in order to avoid dynamic query and still able to concatenate the query based on a condition?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 07:49:47

Like Fred, I must admit that I didn't follow what you are trying to do in the dynamic queries. Especially in the first query I am not seeing why a dynamic query would be needed there.

Regardless, if you need to use the count from on query in another query, there are all kinds of ways to do it without using a dynamic query. You can even calculate the count and use it in the same query. Here is an example:

CREATE TABLE #tmp (col1 FLOAT);
INSERT INTO #tmp VALUES (1),(2),(3),(4),(11),(22);

SELECT col1,
       col1 / COUNT(*) OVER(),
       COUNT(*) OVER()
FROM   #tmp;

DROP TABLE #tmp;

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-21 : 08:08:02

Sunita,
COUNT(*) OVER() is my answer. Thank you. Now how to make sure that the COUNT(*) is not zero?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 08:23:57

quote:
Originally posted by rama108

Sunita,
COUNT(*) OVER() is my answer. Thank you. Now how to make sure that the COUNT(*) is not zero?

Thanks.

You can add a condition to check for it in the query - for example:

CREATE TABLE #tmp (col1 FLOAT);
INSERT INTO #tmp VALUES (1),(2),(3),(4),(11),(22);

SELECT col1,
		-- use null if count is zero.
       col1 / NULLIF(COUNT(*) OVER(),0), 
       -- use one if count is zero.
       col1 / CASE WHEN COUNT(*) OVER() = 0 THEN 1 ELSE COUNT(*) OVER() END,
       COUNT(*) OVER()
FROM   #tmp;

DROP TABLE #tmp;

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-21 : 08:55:53

Thank you.
Another question related to dynamic sql is:
I have the following sql statement, how do i avoid using dynamic sql

SET @sql = N'select id, name
from tableA
where id = ' + @id
IF(@var IS NOT NULL)
BEGIN
SET @sql = @sql + ' AND name in ('+@name+')
END
EXEC(@sql)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 09:18:19

Assuming a comma-separated list in @name, you should be able to use a query like this:

SELECT
	id,
	name
FROM
	tableA
WHERE
	id = @id
	AND 
	(
		@var IS NULL 
		OR ','+@name+',' LIKE '%,' + name + ',%'
	)

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-21 : 09:39:19

Thank you that works for name. But what if I had comma delimited IDs which are numeric? like below:

SET @sql = N'select id, name
from tableA
where name = ' + @name
IF(@var IS NOT NULL)
BEGIN
SET @sql = @sql + ' AND id in ('+@id+')
END
EXEC(@sql)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 09:57:40

Ideally you should split the comma-separated list into a virtual table and then join on that table. But a quick and dirty way would be use the same pattern as we used for name, like this:

AND ','+CAST(@id AS VARCHAR(8000))+',' LIKE '%,' + CAST(id AS VARCHAR(32)) + ',%'

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-21 : 11:06:19

Sunita,
That worked beautifully. I sincerely thank you for your time. I have another question for you. I have a cursor within a cursor and I want to get rid of cursors and use an alternative to nested cursors. How can I do this? Here is my code:


DECLARE DCursor cursor FOR
SELECT ID, Name FROM #tblD
order by Name DESC 
OPEN DCursor
FETCH NEXT FROM DCursor INTO @Id, @Name
	WHILE @@FETCH_STATUS = 0
	BEGIN
	DECLARE DECursor cursor FOR
	SELECT Id FROM #tblD
	WHERE Id = @Id -- this @Id is the reason why I am using a cursor. Is there a way that I can use this @Id value with a While statement? 	OPEN DECursor
	FETCH NEXT FROM DECursor INTO @Id
		WHILE @@FETCH_STATUS = 0
		Begin

		END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 11:47:46

I have not run the code but reading through it, it looks like there is some kind of logical error in it - you are getting a value into @id but then updating that again in the inner query. Unless you are doing something special in the inner while loop, I am not able to follow the logic.

Can you describe what you are trying to calculate? There may be easier ways to do it in a set-based query. If you can post some sample input data and the output you are expecting, that would be very useful.

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-21 : 12:55:48

Sunita,
Set based query could be the answer. I will post a better code this afternoon and explain what I am trying to do line by line.

Thanks again for your time.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-21 : 15:14:29

In regards to the comma separated lists; using a LIKE comparison may well work for your situation. But, if you have larger datasets you may find that parsing the string into a table may work faster.

Here is one such splitter/praser fucntion:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-21 : 17:02:24

Lamprey,
I followed the example and created a variable table which stores the IDs and it has sped up the process and working very well. Thank you for your help. Sunita also suggested the same type of aproach. Is there any good article explaining how to avoid nested cursors?

Thank you for your time.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-21 : 17:34:08

I don't know of any articles that show HOW to avoid nested cursors. Bascially, just don't do it! :) But, if you do not work with SQL a lot it can sometimes be difficult. However, you should always try to think and implement solutions in a set-based way (when dealing with SQL). A set-based solution is almost always the better solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources