Max Query: slow

Showing posts with label slow. Show all posts

Wednesday, March 28, 2012

Help with SPs

Hi there...I'm noticing my pages are running a little slow and I'm
wondering if two SPs I have on every page are the culprits; any way we
can make these more efficient? There are three tables involved:
Zipc: this contains all the zip codes, latitudes and longitudes in the
US
Addr: this contains a user's mailing address
Adve: this contains an advertiser's mailing address
I am pulling content based on a site visitor's zip code (regardless of
whether they're a user or an advertiser), so I need to go into the Zipc
table and pull the Latitude and Longitude so I can determine a radius.
First, I get the latitude and longitude:
CREATE PROCEDURE getlocal
@.user nvarchar(30)
AS
select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
Uniq=@.user)
GO
'in other words, I'm telling the database to get me the latitude and
longitude from the Zipc table where the zip code in that table is equal
to the zip code in the Addr table where the User (or advertiser) is the
one viewing the site
Then, after getting the latitude and longitude and establishing the
outermost reaches of the radius, I pull the five advertisements closest
to the user in no particular order:
CREATE PROCEDURE getmorelocal
@.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
decimal(18,9)
AS
select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
ORDER BY newid()
GO
'in other words, give me five advertisements where their latitudes and
longitudes are contained within the parameters.
Any advice would be appreciated; thanks.Maybe this will help?
http://databases.aspfaq.com/database/how-do-i-create-a-store-locator-feature.html
"Erik Lautier" <lautier@.gmail.com> wrote in message
news:1168881086.981248.69840@.51g2000cwl.googlegroups.com...
> Hi there...I'm noticing my pages are running a little slow and I'm
> wondering if two SPs I have on every page are the culprits; any way we
> can make these more efficient? There are three tables involved:
> Zipc: this contains all the zip codes, latitudes and longitudes in the
> US
> Addr: this contains a user's mailing address
> Adve: this contains an advertiser's mailing address
> I am pulling content based on a site visitor's zip code (regardless of
> whether they're a user or an advertiser), so I need to go into the Zipc
> table and pull the Latitude and Longitude so I can determine a radius.
> First, I get the latitude and longitude:
> CREATE PROCEDURE getlocal
> @.user nvarchar(30)
> AS
> select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
> Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
> Uniq=@.user)
> GO
> 'in other words, I'm telling the database to get me the latitude and
> longitude from the Zipc table where the zip code in that table is equal
> to the zip code in the Addr table where the User (or advertiser) is the
> one viewing the site
> Then, after getting the latitude and longitude and establishing the
> outermost reaches of the radius, I pull the five advertisements closest
> to the user in no particular order:
> CREATE PROCEDURE getmorelocal
> @.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
> decimal(18,9)
> AS
> select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
> Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
> BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
> WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
> ORDER BY newid()
> GO
> 'in other words, give me five advertisements where their latitudes and
> longitudes are contained within the parameters.
> Any advice would be appreciated; thanks.
>|||Aaron,
Thanks. I actually don't need to get too specific with the distances,
so trig functions are a little more than is necessary right now. What
concerns me more is to have the best syntax for the SPs I listed...any
advice on that? For example, rather than having a compound statement
in the first SP, would it be better to break it down into two SPs where
I get the Zip in the first one and then the lats and longs in the
second one? Or, conversely, would it be better to wrap *everything* up
into just one SP?
Erik
Aaron Bertrand [SQL Server MVP] wrote:
> Maybe this will help?
> http://databases.aspfaq.com/database/how-do-i-create-a-store-locator-feature.html
>
>
> "Erik Lautier" <lautier@.gmail.com> wrote in message
> news:1168881086.981248.69840@.51g2000cwl.googlegroups.com...
> > Hi there...I'm noticing my pages are running a little slow and I'm
> > wondering if two SPs I have on every page are the culprits; any way we
> > can make these more efficient? There are three tables involved:
> >
> > Zipc: this contains all the zip codes, latitudes and longitudes in the
> > US
> > Addr: this contains a user's mailing address
> > Adve: this contains an advertiser's mailing address
> >
> > I am pulling content based on a site visitor's zip code (regardless of
> > whether they're a user or an advertiser), so I need to go into the Zipc
> > table and pull the Latitude and Longitude so I can determine a radius.
> >
> > First, I get the latitude and longitude:
> >
> > CREATE PROCEDURE getlocal
> > @.user nvarchar(30)
> > AS
> > select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
> > Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
> > Uniq=@.user)
> > GO
> > 'in other words, I'm telling the database to get me the latitude and
> > longitude from the Zipc table where the zip code in that table is equal
> > to the zip code in the Addr table where the User (or advertiser) is the
> > one viewing the site
> >
> > Then, after getting the latitude and longitude and establishing the
> > outermost reaches of the radius, I pull the five advertisements closest
> > to the user in no particular order:
> >
> > CREATE PROCEDURE getmorelocal
> > @.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
> > decimal(18,9)
> > AS
> > select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
> > Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
> > BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
> > WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
> > ORDER BY newid()
> > GO
> > 'in other words, give me five advertisements where their latitudes and
> > longitudes are contained within the parameters.
> >
> > Any advice would be appreciated; thanks.
> >|||On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
wrote:
>Thanks. I actually don't need to get too specific with the distances,
>so trig functions are a little more than is necessary right now. What
>concerns me more is to have the best syntax for the SPs I listed...any
>advice on that? For example, rather than having a compound statement
>in the first SP, would it be better to break it down into two SPs where
>I get the Zip in the first one and then the lats and longs in the
>second one? Or, conversely, would it be better to wrap *everything* up
>into just one SP?
The sub-selects return small sets, can't see any style problems.
J.|||Ok...having removed those two SPs on a couple test pages, it's apparent
that they are definintely the culprits...perhaps I've just got a slow
SQL Server on my host?
JXStern wrote:
> On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
> wrote:
> >Thanks. I actually don't need to get too specific with the distances,
> >so trig functions are a little more than is necessary right now. What
> >concerns me more is to have the best syntax for the SPs I listed...any
> >advice on that? For example, rather than having a compound statement
> >in the first SP, would it be better to break it down into two SPs where
> >I get the Zip in the first one and then the lats and longs in the
> >second one? Or, conversely, would it be better to wrap *everything* up
> >into just one SP?
> The sub-selects return small sets, can't see any style problems.
> J.|||On 15 Jan 2007 14:16:51 -0800, "Erik Lautier" <lautier@.gmail.com>
wrote:
>Ok...having removed those two SPs on a couple test pages, it's apparent
>that they are definintely the culprits...perhaps I've just got a slow
>SQL Server on my host?
Do you have the proper indexes for the subs to run against? How to
they run on their own?
J.
>JXStern wrote:
>> On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
>> wrote:
>> >Thanks. I actually don't need to get too specific with the distances,
>> >so trig functions are a little more than is necessary right now. What
>> >concerns me more is to have the best syntax for the SPs I listed...any
>> >advice on that? For example, rather than having a compound statement
>> >in the first SP, would it be better to break it down into two SPs where
>> >I get the Zip in the first one and then the lats and longs in the
>> >second one? Or, conversely, would it be better to wrap *everything* up
>> >into just one SP?
>> The sub-selects return small sets, can't see any style problems.
>> J.

Monday, March 26, 2012

Help with SLOW SQL Server performance

I'm still new to SQL Server so some of my lingo/verbage may be incorrect, please bare with me.

The company I work for relies strictly on ASP and SQL Server for 85% of it's daily operations. We have some Access projects and some VB projects as well, but for the majority it's ASP and SQL Server.

Previously we had 2 T1 lines with something like 3MB a piece and a handfull of Dell Servers. Our main server is also a Dell running Windows Server 2003 and is hosted through a reputable company here in town. They have a host of fiber lines running all over so I know we're getting good throughput. We've actually just upgradded to a DS3 but we're still working out the kinks with that. Anyway, I just want to eliminate that up front - we have great connection speeds.

The problems lies, I believe in our database design. The company supposedly had a DBA come in and help setup the design some 3 or 4 years ago, however even with my limited knowledge I feel like something is just not working right.

Our main table is "Invoices" which is obviously all of our Invoices, ever. This table has an Identity field "JobID" which is also the Clustered Index. We have other Indexes as well, but it appears they're just scattered about. The table probably 30-40 fields per row and ONLY 740,000 rows. Tiny in comparison to what I'm told SQL Server can handle.

However, our performance is embarassing. We've just landed a new client who's going to be brining us big business and they're already complaining about the speed of their website. I am just trying to figure out ways to speed things up. SQL is on a dedicated machine I believe with dual Xeon processors and a couple gigs of ram. So that should be ok. THe invoices table I spoke of is constantly accessed by all kinds of operations as it's heart of what we do. We also have other tables such which are joined on this table to make up the reporting we do for clients.

So I guess my question is this. Should the Clustered Index be the identify field and is that causing us problems? We use this field alot for access a single Invoice at a time and from what I understand this makes it a good Clustered Index, because the index IS the jobID we're looking for. But when it comes time to do reporting for a client, we're not looking at this field. We just pull the records for that Clients Number. And we only have 1400 clients at this point. So if we were to make the "ClientID" field the Clustered Index, it would much faster to Zero in on the group of Invoices we wanted because the ClientID is ALWAYS included in our queries.

But because a "DBA" came in to design this setup, everyone is afraid to change it. I guess it's hard to explain without people sitting here going through the code and look at the structures of all our tables - but I guess what I need is like a guide of what to do to easily increase performance on SQL Server and the proper use of Clustered and Non-Clustered Indexs and how to mix and match those.

Sorry I wrote a book.
Ideas? This place has always helped me before, so thanks in advance!The first lesson (ok, first after the "it is not your data" lesson), is to fix problems, and not theories. In order to pin down the exact problem, you have to amass approximately one mountain (mt) of data. Fire up perfmon on both the web server and the DB server. On both look at the following counters:

Memory: pages/sec should be < 100
Processor: %utilization should be < 75%
System: Processor Queue Length should be < 2 * number processors you have
System: Context Switches/sec should be < 10,000

If any of these are significantly out of whack, then you can try to drill down into any one of those.

On the database server side, Profiler and Query Analyzer are going to be your best friends. Trace
Stored Procedures: RPC Completed and
TSQL: SQL BatchCompleted
Look for anything with a duration over 1000. If you see a particular procedure/query commonly running over 1000, then start working over that query. A query that is run once or twice a day is not much to worry over.

In Query Analyzer, use sp_who2 to monitor for blockinig occasionally, and especially during slow times. It could be you just have some interface process that is locking up tables at times throughout the day.

Short of writing a book, this should get you started.|||MCrowley - Thanks for posting back and offering me a starting point on actualy fixing visual problems instead of guessing on what may be the cause. I'll look into the suggested tools and post back on my findings.

Much appreciated.|||Who's the Admin of the Box?

The FIRST thing I would do is to make sure all of your backups and transaction log dumps are ok and successful, by restoring the dumps to a test box.

Do you have restore procedures in place to recover in case of a problem? No? Thatw ould be my second task.

Do the developers use sa to build their code?

I'd revoke all sa access to the database as my third step and make sure only 1-3 individuals hav sa authority...hopefully non developers.

OK, so 4th I'd check for blocking (so_who2 active) and I'd also look at the locks (sp_locks). If you see a lot of table locks, you have a problem...most likely poorly design code.

I would the run a trace and make sure the trace writes to a sql server table.

Here's a template I made up. Make sure you change the host in the filter to a machine id that you are going to test on. Just make sure you change the extension back to .tdf.

I would however seriusly recommend that you hire a qualified dba.

what part of the world are you from?

I got some spare evenings...got some virtual PC going over there?|||The box is administrated by the hosting company primarily. We have limited access. It is our server, but it's on their network in their racks so they handle most of those adminstrative tasks such as updates/hardware/etc. Myself and the rest of the IT dept (6 of us now) all Domain Administration access in Active Directory and access to modify the DB as we see fit. But we don't have an in-house DBA and the guy who HAD the most knowledge got canned for just reasons. So...

We have Vertias in place for Back-Ups that are run every night. I don't know the ins and outs of whether they're full back ups or sequential or what have you, but I do know they work and we recently had one of our staff accidentally omit a where clause on an update which required us to restore a recent backup - so yes, that is tested and working just fine.

Developes us sa to build code? I'm assuming you mean the sa account in SQL Server, but again I could be wrong. I'm learning as I go here. There was "master" account created if you will that is used to access the server throughout all of our production and test code. This is not the "sa" account, but one that was created. When we develop code, we have our own logins and access rights and typically just right the code right in ASP or use Query Anaylzer to test first. When the code is put into production, it uses a general connection string using the master account I spoke of. I hope that answers this.

We do get blocking from time to time, but it's not every day. Still, it happens more often that I'd like. We also get frequent dissconnects. The company we were originally hosted with (we're 90% in house now except for this Server box) claims it's our Firewall and Router. We have no problems with lost connections from anywhere else so we fell this is an issue on their end. But getting back to the point. We do occassionally have blocks. I haven't really investigated them myself, but I can do that.

I've been running a few traces since the first suggestions posted and the trace gets to 15,000 rows in less than a minute. Using the Duration template, I've got to scroll up several pages from the bottom to stop seeing durations over 1000 so there's obviously some performance gains to be made there. Our worst one is a SP that routinely goes over 11,000 for the duration and is ran frequently.

Finally, I'm in the US - OHIO to be exact and we obviously do need a DBA. We've discussed doing that but would like to fix what we can on our own. So we can learn this stuff, and cut down on the cost and time the DBA will actually need to be here.

We do have and VPN and etc setup, but obviously I can't being giving out that kind of info over a message board nor do I have the authority to give it out at all. The company is pretty private with their data. But I DO really appreciate the suggestions and I'll have a look and the attached template.

Much thanks!|||Well I would open up that stored procedure and run a test on it with a SHOW EXECUTION PLAN ([CTRL]+K) and look at the results. Look for scans of any kind.

It could also be that you have a bad plan in PROCACHE. If it is a bad plan you'll need to recompile it.|||OHIO! Blind dude, get over there and help him out...|||Okay now this is where you've exceeded my brain capacity. I executed the SP in Query Analyzer and did the (CTRL + K) thing for showing the execution plan. I also just selected that from the action drop down, but I'm not getting anything so please teach me, wise one! :)

Also how can I check my PROCACHE. Sigh, I can just a see a link to Amazon.com for "SQL Server for dummies" coming my way.

Thanks again for all your help thus far.|||If you did both (selected it, then did CTRL+K), then I would most likely say that you turned it off.

Just go to the menu and select then run the sproc.

I did think you can see the cache, but you can clear it out. DBCC FREEOROCACHE...but that's not a good thing since it will cause everything to have to be recompiled.....wich will slow everything down the first time it's called.

Here's a test.

Make sure the SHOWPLAN is on

Execute the procedure in Query Analyzer...not the length of time and look at the plan

Then do sp_recompile <sprocname>

Execute the proc again. It will take longer because of the compil

Execute the sproc again, then note the time.

IF it is much fatser, then it's a bad plan.

If it's not then it still could be a bad plan and it has no choice.

You might want to show us the sproc.

But tell us what the plan says first

I'm kicking out, but I'll check in in the AM|||The Execution Plan drew out a huge grid diagram with icons and such. Is there anything specific you're looking for?

SPRoc - 1st Run. 40 seconds
SPRoc - 2nd Run. 28 seconds

--Force Recompile
SProc - 1st Run. 1 Mintue 28 seconds
Sproc - 2nd Run. 2nd Run. 30 seconds??
Sproc - 3rd, 4th, 5th, subsequent runs 7 - 15 Seconds! :)

Recompliling the SP seems to have helped, but with your logic that means I have a bad plan?? But I don't even know what a plan is. I'll do some studying and research tonight in prep for more respones.

Cheers!|||You'll be looking for any scans in the output. Also check for wide arrows (indicates many rows returned). Any large table that is getting scanned is probably a bad thing, unless this is a data warehouse/reporting kind of application. Cursors in the stored procedure are also a killer, and usually end up being the first target of any code re-writes.

If you can post the code of the stored procedure in question, we can take pot-shots at suggesting indexes, but those will depend pretty heavily on your data distribution. Have you tried the Index Tuning Wizard? It should be available in Profiler, and definitely should be in Books Online (which is SQL Server for SQL Server users). If you don't have Books Online (aka BOL) then find it on Microsoft's website, download, and install. We may sound like we know it all, but we cheat. We have BOL open from the minute we get in, to about 2 minutes before we leave.

Wait. Did I say that with my inside voice, or my outside voice?|||<div style=Columbo>
Oh, one other thing...
</div>

If you can see blocking, odds are it is a problem. Get to know dbcc inputbuffer (SPID), and try to chase down the blockers. The blockers could be half (or more) your problem.|||CREATE PROCEDURE [dbo].[Envox_AdjustCallLog]

AS

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

UPDATE
Envox_CallLog
SET StartTimeAdj=CallStartTime,
EndTimeAdj=CallEndTime,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='EST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-1,CallStartTime),
EndTimeAdj=DATEADD(hour,-1,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='CST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-2,CallStartTime),
EndTimeAdj=DATEADD(hour,-2,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='MST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-3,CallStartTime),
EndTimeAdj=DATEADD(hour,-3,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='PST' AND Adjusted=0 AND Completed=1

UPDATE
Envox_CallLog
SET StartTimeAdj=DATEADD(hour,-5,CallStartTime),
EndTimeAdj=DATEADD(hour,-5,CallEndTime),
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE ZipCodes.TIME_ZONE='PST-2' AND Adjusted=0 AND Completed=1
GO

We have an Envox telephony Server in house. This SP is apparently adjusting all the logged call times based on their time zones. This is just one of many problem queries that are taking a while to run. I imagine the 3 joins per update on top of the ZipCodes table which is probably huge is the main issues.

I'm also not the phone guy, so I'd have to suggest any recommned changes to him as I'd be eaten alive if I messed with it on my own. The index tuning Wizard is running as we speak, so hopefully it comes back with some good suggestions.|||I can tell you right now, you're gonna see a scan on Properties

ON LEFT(Properties.Zip,5) = ZipCodes.Zip

Do this for us. Run the code below and post the Results

EXEC sp_spaceused Properties
GO
EXEC sp_spaceused ZipCodes
GO
EXEC sp_spaceused ZipCodes
GO

I can also tell you that I know what he's doing, he's turning everything into GMT Times. Now I've never been a big fan of derived columns, but man this is isure where this would be a good thing. Hell I might even say a TRIGGER might have been better than here. Also a derived column for the zip code so they don't need to use LEFT on the zip column in properties. That's called a stage 2 or nonsargable predicate and will always cause a scan.

Also, do you know how to script a table and get the DDL in Enterprise Mangler? Look at the sticky at the top of the forum here and read the instructions if you don't.

I betcha we can fix it up....|||Oh, and I like the error handling he has in there as well...is this run from and interface or a scheduled batch job...and I wonder about the very arbitrary

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

It's gotta be batch...

He really needed to have a batch window table so he doesn't miss anything...here's the problem with the way he's doing it...if the proc doesn't run for a while...there's no determining how long this damn thing will run...he's just leaving the earliest inserts alone...

Matt, wadda ya think, 2 mil inserts a day, TRIGGER? or Derived Column?|||Looks like a pretty easy kill, but if you have a test server, this will definitely need to be tested. Even if you don't have atest server, this should be tested. Try replacing all four updates with this:

UPDATE
Envox_CallLog
SET StartTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallStartTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallStartTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallStartTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallStartTime)
else CallStartTime end, -- covers EST
EndTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallEndTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallEndTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallEndTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallEndTime)
else CallendTime end,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
WHERE Adjusted=0 AND Completed=1

It will save you 3 go rounds on the table, which may translate to quartering the run time. Even if you get half an improvement, it should be good.|||Still gonna scan

But there will a perf boost...you change the old and new queries into selects to see what the return|||That's interesting. Why are the times be logged as different zones?

The server should be using the default time of the server.

How are you getting different time zones for the calls? This has to be a web app with 2 million hits a day, can't be distributed...

That's very curious how it's artichected...

Can you describe how the users us the app?|||OHIO! Blind dude, get over there and help him out...
Be happy to. My company is located in Columbus, and one of the services we offer is DBA outsourcing. I have a client that used to use a full-time dba, but we now cover their requirements with less than 10 hours per week.
If your company is interested in either consulting or dba support, just let me know.|||I doubt they have 2 mil inserts a day, but I could be wrong. Certainly not to a single table, I hope. You are right that this is bound to scan. You may be able to put an index on the Adjusted column. It is just a tad counterintuitive, but if the statistics are kept up to date, the optimizer should see only a very few rows with Adjusted=0, against tons with Adjusted = 1

After that, the first update has to be re-written to say something along the lines of

AND StartTime between dateadd (mi, 200, StartTimeAdj) and dateadd (mi, -200, StartTimeAdj)

provided there is an index on StartTime. That should be easy to switch around if the index is actually on StartTimeAdj.

I will have to think about the trigger vs. Derived Column. I heard somewhere that Computed Columns are not actually stored, but computed on each use. Not sure that is right, though.|||BOL has the following. That would be some heck of a computed column though. I wonder if a compouted can use an index? If it does it would help out with the zip problem.

computed_column_expression

Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.

Note Each row in a table can have different values for columns involved in a computed column, therefore the computed column may not have the same value for each row.

The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.|||The update statement seems to imply that each Invoice goes to a single JobID (sounds normal), but each Invoice has only a single property?

As for why the multiple Time Zones, who knows what business model decided this was a good idea. Seems that all calls are "synchronized" using the original invoice's zip code, so no fair going to the next time zone and calling in about your invoice.

I wonder how they handle Indiana and Arizona during Daylight Savings Time.|||I wonder if a compouted can use an index? If it does it would help out with the zip problem.Did you read what you posted from BOL ?!?!

If I were going to implement on multiple servers (or there was any chance that I might someday want to do so), I'd probably use UCT/GMT to store DATETIME values instead of local time. Using GUIDs and UCT are two key points I've found to keeping things simple as you scale up.

-PatP|||Awesome, awesome lots of feedback. I just returned from lunch so I haven't tried anything out yet, but I thought I'd post back what I could.

I can't get too technical, but our company does mystery shopping. What we're looking at right is obviously a stored procedure which converts times for different time zones.

We have at home employees that will call into our Envox Telephony server to pick up a job and then once connected there routed through some processes and an external number is dialed and they're connected to whom they're supposed to talking to. All the while their calls are recorded and the times they called are logged. Because the table is using time stamps, all the times are in the local time zone. This SProc, I presume is being used to convert all the times into the actual time for the call destination.

So if the employee was routed to California for their call, then the local time is converted to whatever time is would have been in Cali at the time of the call. Then is for reporting purposes obviously.

We have automated VB apps in place that run throughout the day doing different processes - this Sproc gets called from that automated app probably every 30 minutes or so. So roughly 48 times a day.

Name Rows Reserved Data Index Unused
Envox_CallLog 164627 30968 KB 16600 KB 88 KB 14280 KB
Properties 68628 131880 KB 82984 KB 10040 KB 38856 KB
Invoices 718950 3575416 KB 2871416 KB 165576 KB 538424 KB
ZipCodes 42567 5200 KB 5168 KB 16 KB 16 KB|||Computed columns can be indexed. I had to do it when a guy tried to join a varchar column to an int column. Not pretty, when the table was getting 2 - 300 lookups per second.

So, add a column to properties as left(zip, 5). So much for the international market, I guess. But then, that was a debate all to itself in the design forum, if I recall.|||Continuing on - Yes each invoices has a single JobID and single Property as each of our Jobs are placed on one property at a time. If we need to do more than one property, we do more than one job.

2 mil inserts a day? Not even. We've been going for 3 or 4 years now and we're upto 800,000 Jobs roughly, but there IS alot of activity going on. I should do some queries on how many tables and things we have and what kind of traffic is in and out of the SQL server all day. 2 mil inserts is probably a stretch. But I could bet we hit 500,000 transactions easy.|||It's all index seek baby

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([Zip+4] char(10), Zip AS LEFT([Zip+4],5))
GO

SET ARITHABORT ON
GO

CREATE INDEX IX1 ON myTable99(Zip)
GO

INSERT INTO myTable99([Zip+4])
SELECT '07052-0000' UNION ALL
SELECT '12345-1234' UNION ALL
SELECT '00000-0000'
GO

--[CTRL]+K

SELECT *
FROM myTable99
WHERE Zip LIKE '0%'
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

First thing I do is add a computed column zip in your properties table..it will have 0 impact to your application, and has the potential to speed up this existing query by changing it to reference that column.

That's a low impact/ high reward fix.|||Those tables look awfully lean on indexes (based on index size). Can you run sp_helpindex on each of those tables? Looks like this is coming down to design of the application, and how you can mitigate some of the bigger flaws. The tables are not huge, but as Brett points out, the scans are killing you.|||Well your scanning properties with 68,000 rows, and the zip fix I posted will fix that.

Now post the DDL, make sure you included all the contraints and indexes

Let me ask this, do you have a dev server where you can test these chnages out? Do you have the authority to ALTER database objetcs, like add a computed column?|||Okay, I'm a horrible typist so please forgive all the missed words and etc. I'll try to clean up my act a little.

Everything we do is on a testing server first and moved "live" once we're confident it's working as it should. This goes from IIS to SQL Server to Access to whatever. I do have permissions on the Database to do anything I need to do. I can do all of this stuff on the testing server, but I would have to go over the changes with the rest of the IT staff before I moved anything into production.

Let me run the HelpIndex SP and post back the findings.|||Envox_CallLog
PK_Envox_CallLog clustered, unique, primary key located on PRIMARY Call_ID

Properties
PK_Properties clustered, unique, primary key located on PRIMARY PropID
Properties15 nonclustered located on PRIMARY PropID, Property Name
Properties29 nonclustered located on PRIMARY PropID, ZIP, Country
Properties40 nonclustered located on PRIMARY PropID, ClientID
Properties41 nonclustered located on PRIMARY ClientID

Invoices
Invoices16 nonclustered located on PRIMARY ShopID
Invoices23 nonclustered located on PRIMARY ClientID, RcvdDate, AForm
Invoices31 nonclustered located on PRIMARY PropID, ShopID, ClientID
Invoices32 nonclustered located on PRIMARY ClientID, RcvdDate
Invoices34 nonclustered located on PRIMARY FU_Expires
Invoices35 nonclustered located on PRIMARY Select_Shop_Date
Invoices36 nonclustered located on PRIMARY FU_PDFed
Invoices38 nonclustered located on PRIMARY PropID
Invoices39 nonclustered located on PRIMARY ClientID
PK_Invoices clustered, unique, primary key located on PRIMARY JOBID

ZipCodes does not contain any Indexes.|||These are useless, you have 2 other indexes that would take care of that

Invoices38 nonclustered located on PRIMARY PropID
Invoices39 nonclustered located on PRIMARY ClientID

And you need a zip code index on the computed column like I posted.

Now what about the DDL|||I am surprised that the ZipCodes table has no indexes. I should think they would put a PK on the Zip column. Heck, add time_zone, and you have a covering index.

Just for curiosity, the Envox system is not in your control, but is the database? I know some vendors get awful uppity, when you start pointing out where indexes should be, and how table designs could be improved, etc.. They can even say your support contract is suspended for such things. Not a nice place to be caught when the fecal material is introduced into the atmospheric circulation machinery, if you know what I mean.|||Alter Table Properties
Add Zip5 as left(Zip,5)

I did that to add the computed column to the Properties table. Hopefully that was right. I then added an index to that column as requested. Running the old SProc as is, it was 17 seconds this time around. Using the new SProc which was altered to join on the new computed column instead of the old column

CREATE PROCEDURE [dbo].[Envox_AdjustCallLog2] AS

UPDATE
Envox_CallLog
SET StartTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallStartTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallStartTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallStartTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallStartTime)
else CallStartTime end, -- covers EST
EndTimeAdj=case when ZipCodes.TIME_ZONE='PST-2' then DATEADD(hour,-5,CallEndTime)
when ZipCodes.TIME_ZONE='PST' then DATEADD(hour,-3,CallEndTime)
when ZipCodes.TIME_ZONE='MST' then DATEADD(hour,-2,CallEndTime)
when ZipCodes.TIME_ZONE='CST' then DATEADD(hour,-1,CallEndTime)
else CallendTime end,
Adjusted=1
FROM Envox_CallLog INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID
INNER JOIN Properties ON Invoices.PropID = Properties.PropID
INNER JOIN ZipCodes ON Properties.Zip5 = ZipCodes.Zip
WHERE Adjusted=0 AND Completed=1
GO

I got a run time of 2 seconds on the first run. However I'm conerned now. The old SProc affected 208 rows in total. The new one was 256 or so. It's like the old SProc is adjusting rows back the original time and then fixing them again.

Brett. I'm sorry, but can you explain to me what the DDL is? I think I missed that.|||Actually the Envox server is in house and we do control it, I just don't mess with it personally as I'm clueless when it comes to that sort of thing. However, I've given our top IT guy the link to this thread and he also handles the Envox server and he said this had been an interesting read and he would have no problems updating the SPRoc and adjust things as needed if it mean performance gains. So we're in the clear on that.|||For the DDL, look at the bottom in the FAQ section of this link here

http://www.dbforums.com/t1196943.html

As for as to why, you have to remeber the top part of the code

This

UPDATE Envox_CallLog SET Adjusted=0
WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

Sets the population to be affected in the subsequent DML. So the population is always changing...if you didn't do this you would get zero rows affected, unless soemthing else is modifying the Adjusted column.

Again, in my opinion, this is a poor way to run a batch window process.

I still think that you can not only have a computed column for the zip code (yeah for 2 seconds), but I think you could turn the adjusted time column in to a computed column, and then just eliminate this entire process altogether.

See, the problem with storing derived/data and storing it, is usually as soon as it's computed it becomes stale, and potentially out of synch with the real data.

No a computed column is derived at the time it's requested, so it's "fresh out of the oven"

Also, if the computed column doesn't work (there may be some issues with sub queries), there's nothing to say that this couldn't be a view.

Same concept but more flexibility.

I would hazard to say the majority of us don't use computed columns, so thanks for the exercise.|||You are talking about a computed column to strip off the basic five digit zip code?
You should store the zip and the zip4 separately to begin with. Or add a zip5 column and populate it on insert via a sproc or trigger, since it is probably not volatile data.|||Another thing to watch for, is if the insert into the property table uses one of my <sarcastic>favorite</sarcastic> constructs the insert into with no field list. Adding the computed field or any field would break that insert. Just one more thing to check.|||aye yup...so much for the low impact solution...I guess you could check by looking for

WHERE ROUTINE_DEFINITION NOT LIKE 'INTO%('
OR ROUTINE_DEFINITION NOT LIKE 'INSERT%('

But that wouldn't take into account code based sql

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(Col1 int, Col2 int)
GO

INSERT INTO myTable99
SELECT 1,2
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Col3 int
GO

INSERT INTO myTable99
SELECT 1,2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||I tried posting earlier but I was having trouble accessing dbforums. I'll post up the DDL when I have time, but I've been really busy with meetings and stuff today. We're having a DBA come (Wohoo!!) to help us out and get us back on the right track.

Thanks to everyone who posted ideas and suggestions as it's helped me tremendously to better understand how SQL Server operates and things I can do to fine tune it. I still have much to learn!

To get back on topic, I'm 99.9% certain we don't have any Inserts in any of our code that does not use a field list. Actually, when I mentioned that to the IT guys a couple of them didn't even know you could do that, so I think we're ok there with the computed column.

I do agree the zip and +4 should have been store seperately, but I chalk that up to the programmers learning as they want and just adding stuff over the years. Like I said we're 4 years in and there are so many things that we'd like to fix. But when 80% of our operation is automated, it's tricky to just go in and change things.

Like for instance for EVERY client we have, we have 3 tables. All 3 tables are identical except for the name and the fact that each table holds the info for the client at different stages of the process. Clearly we should of had 1 table and just an additional flag that could be used to differentiate the data at different stages. But then how do you change all the processes that we have in place that rely on all those tables. Aye!

We need and overhaul badly.|||Congratulations on getting some relief in there. Take notes, on what he checks. Sounds like there may be another few stored procedures that will need a bit of tuning, but it is a whole lot easier to detect such problems when you are sitting in front of Profiler and Query Analyzer.

And thanks for being a good poster. Too many times we get "I have problem pls help" posts that we spend more time pulling the guy's teeth looking for clues as to why their hamster died.

Help With Slow SQL Query

This query takes 1 minute to execute...
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the p
rimary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I sol
ved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered inde
x scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple
I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know abo
ut
| your data or needs, but in my general experience I've found that indexes o
n
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used f
or
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical que
ries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Wort
h
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip

>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
50,000 records.
clustered, non-unique index.
index scan against the
>primary key of the table, with a WHERE clause for the
far more complex. I
>solved it by altering the joins and rearranging predicates
use the clustered
>index scan at 100% cost and the execution time on that
query
preceding query is so
>simple I don't know what to do.
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
>
> I don't know about
> found that indexes on
> me this one row
> hand, are used for
> identified by this FK).
> FKBatchID would allow
> increasing your
> that it includes
> query shouldn't
> it needs... Worth
> 50,000 records.
> clustered, non-unique index.
> index scan against the
> far more complex. I
> use the clustered
> query
> preceding query is so|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||Thanks for the clarification, Ray; most of my experience is with large
datawarehouse type applications so light on the insert (during production
hours) and very heavy querying is the direction I'm coming from.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:es0OpkwAEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Try putting a nonclustered index on processcount and fkbatchID if this is
> something you do often, if fkbatchid is your clustered index you only have
> to put a nonclustered index on processcount as the clustered key is
included
> in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
> propertied column) if you are heavy inserts this is a good idea as it
> creates hot spots on the disk as your inserts will fall to bottom of the
> leaf level (reducing page splits and affording you not to have to mess
with
> fill factor) If you are query intensive and light inserts, test out Adam's
> solution.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Chris Gallucci" <chris@.gallucci.com> wrote in message
> news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
index.
the
> primary key of the table, with a WHERE clause for the
> solved it by altering the joins and rearranging predicates
> index scan at 100% cost and the execution time on that query
> simple I don't know what to do.
>

Help With Slow SQL Query

This query takes 1 minute to execute...
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the primary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I solved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered index scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know about
| your data or needs, but in my general experience I've found that indexes on
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used for
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical queries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Worth
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip
>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
>> This query takes 1 minute to execute...
>> SELECT SUM([ProcessCount])
>> FROM [ProcessTable]
>> WHERE [FKBatchID] = 1
>> The table contains about 5,000,000 records.
>> The total record count that matches the WHERE clause is
50,000 records.
>> The table has a clustered index on the primary key.
>> The foreign key in the WHERE clause has a non-
clustered, non-unique index.
>> The execution plan shows a 100% cost on a clustered
index scan against the
>primary key of the table, with a WHERE clause for the
>> [FKBatchID] column.
>> I had a similar problem with another query which was
far more complex. I
>solved it by altering the joins and rearranging predicates
>> in the WHERE clause. This cause the plan to no longer
use the clustered
>index scan at 100% cost and the execution time on that
query
>> went from 2 minutes to 5 seconds. However, the
preceding query is so
>simple I don't know what to do.
>> Please help.
>> ChrisG
>>
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
> >--Original Message--
> >Two questions/suggestions:
> >
> >First of all, why are you clustering on the primary key?
> I don't know about
> >your data or needs, but in my general experience I've
> found that indexes on
> >PKs are used more often for random data retrieval (give
> me this one row
> >identified by this one PK). Indexes on FKs, on the other
> hand, are used for
> >more range-related activity (give me these rows
> identified by this FK).
> >Indeed, for this query, having a clustered index on
> FKBatchID would allow
> >the data to be read contiguously from the disc, greatly
> increasing your
> >performance. So you might consider switching that.
> >
> >Second, you could try covering the non-clustered index so
> that it includes
> >ProcessCount (something like, CREATE INDEX MyIndex ON
> >ProcessTable(FKBatchID, ProcessCount)) ... This way, the
> query shouldn't
> >have to go into the leaves of the cluster to get the data
> it needs... Worth
> >a try, at any rate...
> >
> >
> >"Chris Gallucci" <chris@.gallucci.com> wrote in message
> >news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> >> This query takes 1 minute to execute...
> >> SELECT SUM([ProcessCount])
> >> FROM [ProcessTable]
> >> WHERE [FKBatchID] = 1
> >>
> >> The table contains about 5,000,000 records.
> >> The total record count that matches the WHERE clause is
> 50,000 records.
> >> The table has a clustered index on the primary key.
> >> The foreign key in the WHERE clause has a non-
> clustered, non-unique index.
> >> The execution plan shows a 100% cost on a clustered
> index scan against the
> >primary key of the table, with a WHERE clause for the
> >> [FKBatchID] column.
> >>
> >> I had a similar problem with another query which was
> far more complex. I
> >solved it by altering the joins and rearranging predicates
> >> in the WHERE clause. This cause the plan to no longer
> use the clustered
> >index scan at 100% cost and the execution time on that
> query
> >> went from 2 minutes to 5 seconds. However, the
> preceding query is so
> >simple I don't know what to do.
> >>
> >> Please help.
> >>
> >> ChrisG
> >>
> >>
> >
> >
> >.
> >|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>

Help with slow query please...

I've inherited a piece of SQL that takes an age to run (it's dynamically
created, not in a stored procedure)
---
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA =
tableA.fieldA
INNER JOIN tableC
ON tableC.fieldC =
tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
AND
ISNULL(tableC.fieldF, 0) = 0
ORDER BY
tableA.fieldB
---
I've checked the database when this runs and it shows object locks on tableB
and tableC (at the page and table level).
I changed the SQL so that each table name in the FROM clause has the 'WITH
(NOLOCK)' hint on, but this led to no obvious improvement, and the object
locks remained the same.
So, I looked at the execution plan. Nothing horrific...one nested loop
(55%) and one table spool/lazy spool (32%).
So, I ran an index analysis.
This suggested that I create the following index:
CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
[fieldA] ASC )
However, when I check tableB, it already has the following indexes:
1 - clustered index on fieldC
2 - non-clustered index on fieldA and fieldC
Okay, the for the existing non-clustered index, the left and right columns
have been swapped, and I know that the left column is the all important on,
but I'm not sure what to do here...
I could either:
a - re-jig the non-clustered index to swap the columns around, but I've no
idea whether this will improve things and presumably it may break it for
other queries?
b - remove the index altogether and replace it with a single column index
for fieldA (as there's already a single columned clustered index on fieldC).
Suggestions please....
Thanks
GriffTry,
SELECT DISTINCT
tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN
tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN
tableC
ON tableC.fieldC = tableB.fieldC
and tableC.fieldD IN ('para1', 'para2')
AND tableC.fieldE = 'para3'
AND (tableC.fieldF = 0 or tableC.fieldF is null)
ORDER BY
tableA.fieldB;
Post tables definition including primary keys, DRI constraints and indexes
for a better analysis.
AMB
"Griff" wrote:

> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Griff wrote:
> ISNULL(tableC.fieldF, 0) = 0
This is a problem right here. Never apply a function against a table column
in the WHERE clause: it forces a table scan. Unfortunately in this case
(where you want either 0 or Null), it's going to be tough to create a
sargable criterion. You can try
(tableC.fieldF = 0 OR tableC.fieldF IS NULL)
but I suspect this will be non-sargable as well (due to the OR condition)
You can also try a subquery containing a union:
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN (
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF IS NULL
UNION ALL
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF = 0) C
ON C.fieldC =tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
ORDER BY
tableA.fieldB
Of course, the DISTINCT keyword isn't helping performance. Is there any way
to eliminate that?
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Tried your suggestion, but it actually took a bit longer...(3 minutes cf 2.5
minutes)
However, now got it down to sub 1 second by re-ordering the inner joins!
I think that this is due to the fact that first time I was getting a small
amount of data in tableA and inner joining it with a huge amount of data in
tableB (and a fairly huge amount of data in tableC).
I changed the order so that it now gets the big table first and inner joins
it with the smaller table. I'm not sure WHY it's faster as it's got to be
doing the same net amount of work, but the query plan is significantly
different.
Now, the only thing left to do is to change the ASP code that generates the
dynamic ASP code.
Thanks everyone...
Griff|||Have you consider running DBCC DBREINDEX and/or DBCC SHOWCONTIG against the
largest tables + DBCC INDEXDEFRAG?
"Griff" wrote:

> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Hi Bob
Thanks for the response. As you can see from one of my responses in this
thread, I've actually solved the problem.
Latest incarnation of the SQL simply changed the table from which the first
field is retrieved from:
> SELECT
> DISTINCT tableA.fieldA
To:

> SELECT
> DISTINCT tableB.fieldA
And the query gone from ~3 mins to sub 1 second. Amazing!
Not sure how I would get rid of the DISTINCT clause...it does exactly what
I need it to do.
Anyhow, I've learnt two things from your post [1 - not using a function; 2 -
the word "sargable" (which I had to look up)] so a day definitely not
wasted.
Thx
Griff|||Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in
> this thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the
> first field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does
> exactly what I need it to do.
>
There may be another way to accomplish the same task. Without details about
your database structure, it is impossible to make any suggestions.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Griff,
You did not post DDL (another abbreviation to look up :-)), so we cannot
tell what the keys of the different tables are, and what the real
indexes are.
The fact that changing tableA.fieldA to tableB.fieldA increases the
performance with orders of magnitude indicates that you might not have
properly defined the keys (or unique indexes).
Also, in this case you should pay particular attention to the indexes of
tableC, especially if it contains many rows. Since you dit not post DDL,
I cannot give a real suggestion here.
Gert-Jan
Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in this
> thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the firs
t
> field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does exactly wha
t
> I need it to do.
> Anyhow, I've learnt two things from your post [1 - not using a function; 2
-
> the word "sargable" (which I had to look up)] so a day definitely not
> wasted.
> Thx
> Griffsql

Wednesday, March 28, 2012

Help with SPs

Help with SPs

Help with SPs

Monday, March 26, 2012

Help with SLOW SQL Server performance

Help With Slow SQL Query

Help With Slow SQL Query

Help with slow query please...

Max Query

Blog Archive

About Me