Timeout... - 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)

Timeout...

Author

Topic

kittles3069
Starting Member

17 Posts

Posted - 2008-04-02 : 18:10:30

I am running a query with pattern matching and its timeing out.

The KNOWN(t2) table is a white list containing 100+ records. Tables1-Tables10 each contain between 50,000 and 100,000 records. The result is basically a list of distinct program names and how many times they appear in all of the tables. Is there a way to reduce the code so it runs shorter or is the query just to big??

Any help would be greatly appreciated! Thanks!!!!


SELECT DISTINCT t1.programName, COUNT(t1.programName) AS installs
FROM Table1 CROSS JOIN
     Table2 CROSS JOIN
     Table3 CROSS JOIN
     Table4 CROSS JOIN
     Table5 CROSS JOIN
     Table6 CROSS JOIN
     Table7 CROSS JOIN
     Table8 CROSS JOIN
     Table9 AS t1 INNER JOIN
     KNOWN AS t2 ON ISNULL(t1.programName, '') LIKE t2.Title + '%'
GROUP BY t1.programName
ORDER BY t1.programName

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-02 : 18:18:39

cross joins? wow!!
do you realize what the cross join to 9 tables each having 50.000 records returns???

tells us the exact requirements and some sample data with table structure and then we can help you.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

kittles3069
Starting Member

17 Posts

Posted - 2008-04-02 : 18:33:33

Yeah, i figured. Im just not sure what an alternative would be.. It works fine if I just do like the first 2 tables.. anything past that goes down...

Requirements:
- Count of how many times each record in the KNOWN table appears in each of the 9 other tables.
(this is a pattern match though, so if a record in KNOWN is Adobe Acrobat 4, it needs to return a count for all Adobe Acrobat 4.0 and 4.1, etc...)

KNOWN::
ID Title
------------------------
1 Adobe Acrobat 4
2 Adobe Acrobat 5
3 Adobe Acrobat 6
4 Adobe Acrobat 7
5 Adobe Acrobat 8
6 Adobe Creative Suite
7 Adobe Dreamweaver
etc..

Table1::
ID computerName programName version publisher installDate ItemTotal
------------------------------------------------------------------
1 NAME1 WebFldrs XP 9.50.6513 Microsoft Corporation 20031009 1
2 NAME2 Security Update for Windows XP (KB901214) 1 Microsoft Corporation 20030720 1
3 NAME3 Security Update for Windows XP (KB893066) 2 Microsoft Corporation 20030422 1
etc...

Each of the other 8 tables are of identical format, just different data.

SAMPLE OUTPUT::
programName installs
----------------------
Adobe Acrobat 4 2951
Adobe Acrobat 7 17444
etc...

kittles3069
Starting Member

17 Posts

Posted - 2008-04-04 : 14:21:59

Was the info I provided sufficient or is more needed??

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-04 : 15:22:44

May be this:-

SELECT k.Title AS ProgramTitle,COUNT(t1.ID)+COUNT(t2.ID) + COUNT(t3.ID) +... + COUNT(t9.ID) AS Installs
FROM KNOWN k
LEFT OUTER JOIN 
(SELECT ID,
       ISNULL(ComputerName,'') +  
       ISNULL(programName,'') +
       ISNULL(version,'') +
       ISNULL(publisher,'') AS SearchText
FROM  Table1) t1 
ON t1.SearchText LIKE '%' + k.Title + '%'
LEFT OUTER JOIN 
(SELECT ID,
       ISNULL(ComputerName,'') +  
       ISNULL(programName,'') +
       ISNULL(version,'') +
       ISNULL(publisher,'') AS SearchText
FROM  Table2) t2 
ON t2.SearchText LIKE '%' + k.Title + '%'
LEFT OUTER JOIN 
(SELECT ID,
       ISNULL(ComputerName,'') +  
       ISNULL(programName,'') +
       ISNULL(version,'') +
       ISNULL(publisher,'') AS SearchText
FROM  Table3) t3 
ON t3.SearchText LIKE '%' + k.Title + '%'
.....................
LEFT OUTER JOIN 
(SELECT ID,
       ISNULL(ComputerName,'') +  
       ISNULL(programName,'') +
       ISNULL(version,'') +
       ISNULL(publisher,'') AS SearchText
FROM  Table9) t9 
ON t1.SearchText LIKE '%' + k.Title + '%'
GROUP BY k.Title

kittles3069
Starting Member

17 Posts

Posted - 2008-04-04 : 17:54:35

Ok, thanks... I took that and tweaked it a bit. Now it doesnt time out (well I havent tried with all 10 tables yet) but it got past 3...

SELECT DISTINCT k.Title + ' ' + k.Version AS ProgramTitle, COUNT(t1.ID) AS Installs
FROM KNOWN AS k LEFT OUTER JOIN
(SELECT ID, ISNULL(programName, '') AS SearchText
FROM Table1) AS t1 ON t1.SearchText LIKE k.Title + '%' AND t1.SearchText LIKE '%' + k.Version + '%' LEFT OUTER JOIN
(SELECT ID, ISNULL(programName, '') AS SearchText
FROM Table2) AS t2 ON t2.SearchText LIKE k.Title + '%' AND t2.SearchText LIKE '%' + k.Version + '%'
GROUP BY k.Title + ' ' + k.Version
ORDER BY k.Title + ' ' + k.Version

Anyways, now the Install count is way wrong.
If I keep the Count(t1.ID) + Count(t2.ID)...etc. Its multiplying each returned value by the next, then doubling it.
So if the install count for table1 is 5 and the install count for table2 is 71, it displays at 710. So its doing the 5*71=355*2=710 and so on for the additional tables.

I changed it to just Count(t1.ID) and its doing the 5*71=355 but not doubling it.... It should just be 5+71=76

I dont see where the multiplication comes in.

Is that part of the outerjoin?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-04 : 18:10:29

What indexes do you have on the tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kittles3069
Starting Member

17 Posts

Posted - 2008-04-04 : 18:20:00

I inherited this database from someone else but i dont think there are any.

quote:
Originally posted by tkizer

What indexes do you have on the tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-04 : 18:21:26

It's no wonder it's timing out then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kittles3069
Starting Member

17 Posts

Posted - 2008-04-04 : 18:48:44

I have like zero experience with Indexes.. I'm looking into it now, but could you possible point in a certain direction?

Would i just create indexes on certain columns or by a group of columns (like for each of the outer join queries)??

Thanks in advance for any help.

quote:
Originally posted by tkizer

It's no wonder it's timing out then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-04 : 18:50:39

You need to index programName in each of the tables. Also index Table2.Title.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kittles3069
Starting Member

17 Posts

Posted - 2008-04-07 : 16:39:34

Thanks for the assistance... I'm sure the indexing made a difference but The previous query was returning incorrect counts so I had to ditch it and try a different method.

I got the query working and comes back in under 2 seconds every time.

FYI...


SELECT DISTINCT k.Title + ' ' + k.Version AS ProgramTitle, COUNT(t1.programName) AS Installs
FROM         (SELECT     programName
                       FROM         Table1
                       UNION ALL
                       SELECT     programName
                       FROM         Table2
                       UNION ALL
                       SELECT     programName
                       FROM         Table3
                       UNION ALL
                       SELECT     programName
                       FROM         Table4
                       UNION ALL
                       SELECT     programName
                       FROM         Table5
                       UNION ALL
                       SELECT     programName
                       FROM         Table6
                       UNION ALL
                       SELECT     programName
                       FROM         Table7
                       UNION ALL
                       SELECT     programName
                       FROM         Table8
                       UNION ALL
                       SELECT     programName
                       FROM         Table9) AS t1 INNER JOIN
                      KNOWN AS k ON t1.programName LIKE k.Title + '%'  AND t1.programName LIKE '%' + k.Version + '%' 
GROUP BY k.Title + ' ' + k.Version
ORDER BY k.Title + ' ' + k.Version

quote:
Originally posted by tkizer

You need to index programName in each of the tables. Also index Table2.Title.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources