Query Help - 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 2000 Forums

SQL Server Development (2000)

Query Help

Author

Topic

durilai
Starting Member

13 Posts

Posted - 2006-10-12 : 19:53:03

I have a query that I am having a strange problem with. I created this one:


SELECT TOP 100 PERCENT ISNULL(dbo.Zips.county, N'Unknown') AS ccounty, COUNT(dbo.preparers.ctecid) AS ccount, Zips_1.state 
FROM dbo.Zips Zips_1 
RIGHT OUTER JOIN dbo.Zips ON Zips_1.zid = dbo.Zips.zid 
RIGHT OUTER JOIN dbo.preparers 
LEFT OUTER JOIN dbo.preparerregistrations ON dbo.preparers.ctecid = dbo.preparerregistrations.ctecid ON dbo.Zips.zip = LEFT(dbo.preparers.zip, 5) 
WHERE (dbo.preparerregistrations.regdate BETWEEN CONVERT(DATETIME, '2005-10-1 00:00:00', 120) 
AND CONVERT(DATETIME, '2006-10-11 23:59:59', 120)) 
GROUP BY ISNULL(dbo.Zips.county, N'Unknown'), Zips_1.state 
ORDER BY Zips_1.state, ISNULL(dbo.Zips.county, N'Unknown')

Which pulls a count of members from a table and compares there zip code to a table that will map it to a county. Bottom line it should return two columns first is a county name and second is a count of members. THe above runs in about 5 seconds and is pulling a years worth of data.

Now below is the exact same query, except that I am only pulling like 10 days worth. This query however timesout in enterprise manager:


SELECT TOP 100 PERCENT ISNULL(dbo.Zips.county, N'Unknown') AS ccounty, COUNT(dbo.preparers.ctecid) AS ccount, Zips_1.state 
FROM dbo.Zips Zips_1 
RIGHT OUTER JOIN dbo.Zips ON Zips_1.zid = dbo.Zips.zid 
RIGHT OUTER JOIN dbo.preparers 
LEFT OUTER JOIN dbo.preparerregistrations ON dbo.preparers.ctecid = dbo.preparerregistrations.ctecid ON dbo.Zips.zip = LEFT(dbo.preparers.zip, 5) 
WHERE (dbo.preparerregistrations.regdate BETWEEN CONVERT(DATETIME, '2006-10-1 00:00:00', 120) 
AND CONVERT(DATETIME, '2006-10-11 23:59:59', 120)) 
GROUP BY ISNULL(dbo.Zips.county, N'Unknown'), Zips_1.state 
ORDER BY Zips_1.state, ISNULL(dbo.Zips.county, N'Unknown')

Any help is appreciated.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-12 : 20:13:10

quote:
Originally posted by durilai
...Any help is appreciated...

Put carriage returns in your code to make them easier to read,
and people might actually look at the code to help you.

CODO ERGO SUM

durilai
Starting Member

13 Posts

Posted - 2006-10-13 : 02:48:48

Its fixed.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 02:58:41

Why double joins to dbo.Zips?
Post some sample data and we'll come up with something clever.

Oh. And also post the expected output based on that sample data.

Peter Larsson
Helsingborg, Sweden

durilai
Starting Member

13 Posts

Posted - 2006-10-13 : 03:18:53

You are right that second join was redundant, I updated the query to this:


SELECT TOP 100 PERCENT ISNULL(dbo.Zips.county, N'Unknown') AS ccounty, COUNT(dbo.preparers.ctecid) AS ccount, dbo.Zips.state 
FROM dbo.Zips 
RIGHT OUTER JOIN dbo.preparers 
LEFT OUTER JOIN dbo.preparerregistrations ON dbo.preparers.ctecid = dbo.preparerregistrations.ctecid ON dbo.Zips.zip = LEFT(dbo.preparers.zip, 5) 
WHERE (dbo.preparerregistrations.regdate BETWEEN CONVERT(DATETIME, '2005-10-1 00:00:00', 120) 
AND CONVERT(DATETIME, '2006-10-13 23:59:59', 120)) 
GROUP BY ISNULL(dbo.Zips.county, N'Unknown'), dbo.Zips.state 
ORDER BY dbo.Zips.state DESC, ccounty DESC

But still the same issue, when the date range is 2005-10-1 to 2006-10-13 it runs in about 3-5 seconds, but with the date range of 2006-10-1 to 2006-10-13 it timesout. My first thought was data but the data from the bad query would be in the other query as well. My table are as follows.

The dbo.zips table looks like this

zipid - 1 (int, pri key, auto number)
zipcode - 95814 (nvarchar)
county - Sacramento (nvarchar)
state - CA (nvarchar)

The dbo.preparers table is as follows:

ctecid - 12541 (int, pri key, auto number)
zip - 95814 (nvarchar)

The dbo.preparersregistrations table is as follows:

ctecid - 95814 (int)
regdate - 10/5/2006 (date)

The relationships are as follows

The prepare table has unique records for each member, which relate to the preparerregistrations table which contains multiple records for each record in the preparer table.

The Zip table ties to the preparers zip code inorder to locate count and state.

The help is appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 03:43:00

Try this

SELECT		ISNULL(z.County, N'Unknown') County,
		ISNULL(z.State, N'Unknown') State,
		ISNULL(COUNT(DISTINCT p.CteCID), 0) CteCIDs,
		COUNT(*) Registrations
FROM		dbo.Preparers p
LEFT JOIN	dbo.Zips z ON z.ZipCode = p.Zip
LEFT JOIN	dbo.PreparersRegistrations pr ON pr.CteCID = p.CteCID AND pr.RegDate >= '20051001' AND pr.RegDate < '20061014'
GROUP BY	ISNULL(z.County, N'Unknown'),
		ISNULL(z.State, N'Unknown')

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 03:47:57

You select one year of data. Maybe some proper indexing would help.

Peter Larsson
Helsingborg, Sweden

durilai
Starting Member

13 Posts

Posted - 2006-10-13 : 19:10:31

That is great, but the problem is I do not want to show counties with 0 ctecid's. The user needs to be able to select a date range and what do you mean by proper indexes? I am trying to learn so bear with me.

THanks a bunch for the query it runs very fast.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 03:06:33

[code]DECLARE @Result TABLE (County NVARCHAR(100), State NVARCHAR(100), CteCIDS INT, Registrations INT)

INSERT @Result
(
County,
State,
CteCIDS,
Registrations
)
SELECT ISNULL(z.County, N'Unknown'),
ISNULL(z.State, N'Unknown'),
ISNULL(COUNT(DISTINCT p.CteCID), 0),
COUNT(*)
FROM dbo.Preparers p
LEFT JOIN dbo.Zips z ON z.ZipCode = p.Zip
LEFT JOIN dbo.PreparersRegistrations pr ON pr.CteCID = p.CteCID AND pr.RegDate >= '20051001' AND pr.RegDate < '20061014'
GROUP BY ISNULL(z.County, N'Unknown'),
ISNULL(z.State, N'Unknown')

SELECT r.*
FROM @Result r
INNER JOIN (
SELECT County
FROM @Result
GROUP BY Country
HAVING SUM(CteCIDS) > 0
) d ON d.County = r.County[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources