Shorten Query? - 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)

Shorten Query?

Author

Topic

kittles3069
Starting Member

17 Posts

Posted - 2008-02-01 : 11:41:44

Hey All,

I have a query that im dynamically creating in asp.net.

I have one table with a white list of computer programs(names only) and one table containes a list of computer users and software installed on their pc, one row per software title.

My query is basically:

SELECT * FROM MainTable
WHERE

Then in asp.net im looping through every title on the white list and adding:
(programName LIKE '%TITLE%')
to the query. There are potentially 1000+ titles on the white list, hence 1000+ LIKE statements.

If i try to execute this query in SQL Management Studio, it takes around 1:40 to complete and basically timeouts in my web applilcations.

Is there a way to optimize the query so its less intensive?

Any help would be appreciated!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 11:44:24

Can you post your exact requirement with table structures and sample o/p?

kittles3069
Starting Member

17 Posts

Posted - 2008-02-01 : 12:01:17

quote:
Originally posted by visakh16

Can you post your exact requirement with table structures and sample o/p?

Thanks for the quick reply... Yes see below.

Requirements: Must display list of all entries from Table1 which match any items in WhiteList Table.

Table1
Columns
computerName varchar()
programName varchar()
version varchar()
publisher varchar()
installDate varchar()

WhiteList
Columns
title varchar()

Sample Query
SELECT * FROM Table1 WHERE (programName <> '') AND (programName LIKE '1310%') OR (programName LIKE '2400%')

Sample Output
1211 Microsoft Data Access Components NULL Microsoft Corporation NULL
1211 Microsoft Office XP Pro NULL NULL NULL
1211 hp officejet 7100 series NULL NULL NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 12:24:11

Can you try either of these?
SELECT * FROM Table1 WHERE ISNULL(programName,'') IN (SELECT title from WhiteList)

or

SELECT t1.* FROM Table1 t1
INNER JOIN WhiteList t2
ON t2.title=ISNULL(t1.programName,'')

kittles3069
Starting Member

17 Posts

Posted - 2008-02-01 : 12:44:06

Thanks for the reply..

I tried both.

The first one tool 46 seconds and displayed 3 records.
The second tool 4 seconds and displayed the same 3 records.

But there are supposed to be 100+ records returned for this.. THe 3 returned are in that list. But im not sure why the others wouldnt be matching? I know looking through the whitelist that some of the titles are truncated. but there are several others which are exact matches, as the 3 records returned were, that arent coming up either..

quote:
Originally posted by visakh16

Can you try either of these?
SELECT * FROM Table1 WHERE ISNULL(programName,'') IN (SELECT title from WhiteList)

or

SELECT t1.* FROM Table1 t1
INNER JOIN WhiteList t2
ON t2.title=ISNULL(t1.programName,'')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 12:47:44

quote:
Originally posted by kittles3069

Thanks for the reply..

I tried both.

The first one tool 46 seconds and displayed 3 records.
The second tool 4 seconds and displayed the same 3 records.

But there are supposed to be 100+ records returned for this.. THe 3 returned are in that list. But im not sure why the others wouldnt be matching? I know looking through the whitelist that some of the titles are truncated. but there are several others which are exact matches, as the 3 records returned were, that arent coming up either..

quote:
Originally posted by visakh16

Can you try either of these?
SELECT * FROM Table1 WHERE ISNULL(programName,'') IN (SELECT title from WhiteList)

or

SELECT t1.* FROM Table1 t1
INNER JOIN WhiteList t2
ON t2.title=ISNULL(t1.programName,'')

SELECT t1.* FROM Table1 t1
INNER JOIN WhiteList t2
ON LTRIM(RTRIM(UPPER(t2.title)))=LTRIM(RTRIM(ISNULL(UPPER(t1.programName),''))

change like this and see

kittles3069
Starting Member

17 Posts

Posted - 2008-02-01 : 12:59:01

quote:
SELECT t1.* FROM Table1 t1
INNER JOIN WhiteList t2
ON LTRIM(RTRIM(UPPER(t2.title)))=LTRIM(RTRIM(ISNULL(UPPER(t1.programName),''))

change like this and see

Thanks for your replies.

Took 11 seconds, but still returned the same 3 records...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 13:07:08

Ok then i guess you are trying for pattern match rather than exact match.try this too & see:-

SELECT t1.* FROM Table1 t1
INNER JOIN WhiteList t2
ON ISNULL(t1.programName,'') LIKE t2.title + '%'

kittles3069
Starting Member

17 Posts

Posted - 2008-02-01 : 13:32:50

quote:
Originally posted by visakh16

Ok then i guess you are trying for pattern match rather than exact match.try this too & see:-

SELECT t1.* FROM Table1 t1
INNER JOIN WhiteList t2
ON ISNULL(t1.programName,'') LIKE t2.title + '%'

Thanks again..

This time, it pulled up all of the correct records. But took 2:14??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 13:36:31

It will be obviously slow because you are trying to perform a pattern match rather than exact match.

kittles3069
Starting Member

17 Posts

Posted - 2008-02-01 : 13:38:59

quote:
Originally posted by visakh16

It will be obviously slow because you are trying to perform a pattern match rather than exact match.

Thanks again for your help...

I understand that. My initial query, which was looping through and generated the like statements, then basically exectuing a query wth 1000 LIKE statements pulled the same results and ran in 1:40.

I was trying to find a way to reduce the time from 1:40.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources