temp storage using sql - 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 2005 Forums

Transact-SQL (2005)

temp storage using sql

Author

Topic

badinar
Starting Member

14 Posts

Posted - 2008-10-07 : 12:40:44

i have these 3 sql queries which will be executed in a single sql server call, all 3 have the same where clause. is it possible to isolate the data from the first query and use it in the next 2 queries instead of using the same where in all 3. this is a big performance hit.

i tried to use with, but seems like applies only to the immediate next query after the with. and using temp tables takes it own time for the inserts.

any idea if this can improved?? help plz?



SELECT TOP 10 Id, ProductNumber, Attributes, ShortDescription  
FROM ProductSearchSuggestions WHERE CONTAINS(SearchText, 'FormsOf(Inflectional, kohler) AND FormsOf(Inflectional, faucet)')

SELECT Count(*)  FROM ProductSearchSuggestions WHERE CONTAINS(SearchText, 'FormsOf(Inflectional, kohler) AND FormsOf(Inflectional, faucet)')

SELECT  pa.attributeid as AttributeId, a.name as AttributeName 
FROM    productattributes pa, attribute a 
WHERE   pa.AttributeId = a.id AND 
    pa.AttributeValue <> 'na' AND 
    pa.AttributeValue <> '0' AND
    pa.productid IN (SELECT PRODUCTID FROM ProductSearchSuggestions WHERE CONTAINS(SearchText, 'FormsOf(Inflectional, kohler) AND FormsOf(Inflectional, faucet)'))
GROUP BY pa.attributeid, a.name

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 13:06:37

SELECT Id, PRODUCTID, ProductNumber, Attributes, ShortDescription into #TEMP
FROM ProductSearchSuggestions WHERE CONTAINS(SearchText, 'FormsOf(Inflectional, kohler) AND FormsOf(Inflectional, faucet)');

select top 10 Id, ProductNumber, Attributes, ShortDescription from #TEMP;

select count(*) from #TEMP;

SELECT pa.attributeid as AttributeId, a.name as AttributeName
FROM productattributes pa, attribute a
WHERE pa.AttributeId = a.id AND
pa.AttributeValue <> 'na' AND
pa.AttributeValue <> '0' AND
pa.productid IN (select PRODUCTID from #TEMP)
GROUP BY pa.attributeid, a.name

badinar
Starting Member

14 Posts

Posted - 2008-10-07 : 18:44:22

thanks for your help!! but temp table insert offsets what is gained of this change..

i came up with something like this... works ok.. but is this even a good way to do sql???...



DECLARE @str VARCHAR(MAX) SET @str = ''
SELECT @str = @str + '''' + Convert(Varchar(Max), Id) + ''','
FROM ProductSearchSuggestions WHERE 
CONTAINS(SearchText, 'FormsOf(Inflectional, kohler) AND FormsOf(Inflectional, faucet)')
SELECT @str = left(@str,len(@str)-1)


EXEC( 'SELECT TOP 10 Id, PRODUCTID, ProductNumber, Attributes, ShortDescription
FROM ProductSearchSuggestions WHERE PRODUCTID IN (' + @str + ')

select count(*) from ProductSearchSuggestions WHERE PRODUCTID IN (' + @str + ')

SELECT pa.attributeid as AttributeId, a.name as AttributeName 
FROM productattributes pa, attribute a 
WHERE pa.AttributeId = a.id AND 
pa.AttributeValue <> ''na'' AND 
pa.AttributeValue <> ''0'' AND
pa.productid IN (select PRODUCTID from  ProductSearchSuggestions WHERE PRODUCTID IN (' + @str + '))
GROUP BY pa.attributeid, a.name
'
)

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 19:01:27

Well, depends on the size of your ProductSearchSuggestions table, creating temp table to hold the record set is probably not the best idea if dealing with small numbers. SQL in your last post is only reusing the query string, the execution result should be exact the same as your 1st post. What are you trying to do with these 3 sqls?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources