Streamline SP
-
This query takes quite a while to run and I'm wondering if there is anything I could do to speed it up?
SELECT ReplyREI.dbo.Reply_PropertyInformation.PropertyAddressState AS STATE, neighborhoodinfo.dbo.Profiles.POPCY, neighborhoodinfo.dbo.Profiles.POPDIF90CY, neighborhoodinfo.dbo.Profiles.ZIP,
neighborhoodinfo.dbo.Profiles.ZIPNAME AS CITY
FROM ReplyREI.dbo.Reply_PropertyInformation RIGHT OUTER JOIN
neighborhoodinfo.dbo.Profiles ON neighborhoodinfo.dbo.Profiles.ZIP = ReplyREI.dbo.Reply_PropertyInformation.PropertyAddressZip
WHERE neighborhoodinfo.dbo.Profiles.ZIP IS NOT NULL
-
how many results is it returning?
-
I returns millions of rows. I have another proc that narrows down the server further, but I wasn't sure about the join..
-
Add this before your query and post the results of the ShowPlan:
Set Showplan_Text On
go
Set NoExec on
go
Also, you can make your query more concise and easier to read if you adopt table aliases instead of replicating the 3-part naming of each object:
SELECT pi.PropertyAddressState AS STATE,
p.POPCY,
p.POPDIF90CY,
p.ZIP,
p.ZIPNAME AS CITY
FROM ReplyREI.dbo.Reply_PropertyInformation As pi
RIGHT OUTER JOIN neighborhoodinfo.dbo.Profiles As p
ON p.ZIP = pi.PropertyAddressZip
WHERE p.ZIP IS NOT NULL
-
Thanks in advance for your help.
StmtText
----------------
Set NoExec on
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT pi.PropertyAddressState AS STATE,
p.POPCY,
p.POPDIF90CY,
p.ZIP,
p.ZIPNAME AS CITY
FROM ReplyREI.dbo.Reply_PropertyInformation As pi
RIGHT OUTER JOIN neighborhoodinfo.dbo.Profiles As p
ON p.ZIP = pi.PropertyAddressZip
WHERE p.ZIP IS NOT NULL
(1 row(s) affected)
StmtText
-------------------------------------------------------------------------------------------------------------------------------
|--Hash Match(Left Outer Join, HASH
[p].[ZIP])=([pi].[PropertyAddressZip]), RESIDUAL[p].[ZIP]=[pi].[PropertyAddressZip]))|--Clustered Index Scan(OBJECT
[NeighborhoodInfo].[dbo].[Profiles].[PK_Profiles] AS [p]), WHERE[p].[ZIP]<>NULL))|--Index Scan(OBJECT
[ReplyREI].[dbo].[Reply_PropertyInformation].[idx_AdvancedSearch] AS [pi]))(3 row(s) affected)
-
You may be able to get a bit more performance out of it by changing this...
WHERE neighborhoodinfo.dbo.Profiles.ZIP IS NOT NULL
...to this...
WHERE neighborhoodinfo.dbo.Profiles.ZIP > '00000'
... which will also exclude "bad" zip codes and "blank" zip codes.
Also, I'd check to see if all of the tables in your SELECT are tables...
I've troubleshot many a slow running query only to find out that it wasn't the query,
it was referencing one (or more) poorly written views. See if some of the tables
are views and see what THEIR execution plans look like.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
THanks for the help...
Susan
-
Index columns with ZIP codes in both tables.
And don't return million rows.
Change this SP to a view. And select in following SP from this view.
It will allow optimiser to narrow hash tables and ease the load on server.
_____________
Code for TallyGenerator -
I think someone needs to look at the data in the tables. How many times is a ZipCode listed in the neigborhood Profiles table? You could be spawning dozens or even hundreds of unwanted (duplicate) records for each property zipcode. Yes, DISTINCT may help but that may be treating the symptom, not the problem.
I think before we simply transfer the problem to, what could turn out to be, a farily bloated denormalized view, I think someone needs to look at the data, figure out why millions of rows are being returned, and correctly define the problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply