Indexing Views optimisation performance question

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

Transact-SQL (2000)

Indexing Views optimisation performance question

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2007-05-04 : 06:03:40

Hi,

I've got a view that picks up and filters lots of data from 2 databases.

I use this view in a few report sprocs, to make sure that my results are consistent (because the data is hideously complex, this has been a major issue before.)

When I run most of the sprocs, the results come back pretty quickly, but for a couple of them, using different criteria to pull back info from the view, the stored procedures run at 18 minutes +.

To illustrate, I've got one procedure where the basic criteria is:

where (casestart <= @paramEndDate and caseend is null)
or 
(casestart <= @paramEndDate and caseend > @paramenddate)

(This identifies items of work outstanding at a date - casestart and caseend are itens fronm the view)
This sproc runs in around 5 seconds.

If I run the exact same sproc, but use this criteria instead:

where (caseend between @paramstartdate and @paramenddate)

The sproc runs in about 18 and a half minutes.

I've tried running the estimated execution plans etc. but I get an 'Invalid object name #TempResults' despite the stored procedure running fine.

I also tried running a trace, but it brought the server to its knees, and hadn't finished running after 2 hours.

I'm guessing that the problem is that the view results are humongous, and the optimiser has somehow indexed some bits of the view but not others. I tried reading up on creating indexed views, but apparently I can't use them because the view drags data back from 2 databases.

I tried creating an index on the view anyway, but apparently the thing needs to be 'schema bound'. I read up on this, but this only seems to apply to functions, not views, so I got scared and stopped.

So, any ideas on how I can optimise querying the view??

Cheers,

Yonabout

yonabout
Posting Yak Master

112 Posts

Posted - 2007-05-04 : 06:35:55

I just realised I did a stupid re the schema binding - I'd missed the bit in books online that said the view had to be schema boud.

The rest of my question / ramble still applies.

Cheers,

Yonabout

Kristen
Test

22859 Posts

Posted - 2007-05-04 : 07:13:25

"optimiser has somehow indexed some bits of the view but not others"

The optimiser will be relying on the Statistics. make sure that they have been updated recently, and consider using "FULLSCAN" to increase the amount of data that the Update Statics considers in creating the new Statistics.

I would rewrite this:

where (casestart <= @paramEndDate and caseend is null)
or (casestart <= @paramEndDate and caseend > @paramenddate)

as

where casestart <= @paramEndDate and (caseend is null OR caseend > @paramenddate)

but the optimiser may be smart enough to realise that they are the same!

Kristen

yonabout
Posting Yak Master

112 Posts

Posted - 2007-05-09 : 10:28:08

OK,

How (and where) do I update statistics?

Also, its the "between" criteria that make it run like a dog. Is there any way I can re-write that more efficiently?

Cheers,

Yonabout

Kristen
Test

22859 Posts

Posted - 2007-05-09 : 13:26:14

"How (and where) do I update statistics?"

EXEC sp_updatestats

should do the whole lot, for the current database - but beware it uses default settings; they will be fine in most cases, but if you already have a carefully crafted system that rebuilds statistics you may want to pause for thought!.

If you use a lot of stored procedures (or parameterized adhoc queries) you may need to empty the cache to get the new statistics used. (If its not a 24/7 system restarting the SQL Service might be the easiest way of achieving that)

There are options in the Maintenance Plan Wizard to create a scheduled job for this.

"Also, its the "between" criteria that make it run like a dog. Is there any way I can re-write that more efficiently?"

I would try creating a composite index on casestart, caseend and see if that helps. Caveat: Adding indexes increases the time to Insert, and sometimes Update, records, so the assumption is that your system needs to Read a record way more times that it Creates/Changes it.

Kristen

yonabout
Posting Yak Master

112 Posts

Posted - 2007-05-11 : 11:02:10

Cool.

I'll see how that goes - thanks for the assist.

Cheers,

Yonabout

yonabout
Posting Yak Master

112 Posts

Posted - 2007-05-23 : 09:08:17

Hi,

It's been a while - I got sidetracked with something else.

I tried out all your recommendations, but they didn't seem to make much difference, and now I'm thinking the problem isn't what I initially thought it was.

If I hardcode the parameter values and run the stored procedure SQL, I've timed it at 15 seconds.

When I compile the exact same code as a stored procedure, then call the stored procedure from a query (e.g. like this:

exec dbo.rptCompletedCasesGraph 'Val1', 'Val2'

it takes 47 minutes.

How can it do that?

Cheers,

Yonabout

Kristen
Test

22859 Posts

Posted - 2007-05-23 : 13:47:43

"How can it do that?"

parameter sniffing ??

Or maybe the Query Plan saved for the Sproc is for a completely different scenario, and is "rubbish" for the parameters you are now giving it.

Or if you are using SP4 maybe you are comparing columns of different datatypes, and the implicit conversion is taking longer under SP4 than it used to before (I forget the details, but this is a known issue, fixable by CASTing the mismatched datatypes I think)

Kristen

yonabout
Posting Yak Master

112 Posts

Posted - 2007-05-24 : 09:22:37

OK,

So have you got any ideas how I can find out whats making it go so slow?

As I said, if I run the sproc code as a query in query analyser, its OK, its just when its compiled that it grinds to a halt.

I am on SP4, so I'll go through and make sure any implicit conversions are made explicitly.

Other than that, how do I make it use a new query plan? Is it just a matter of dropping and re-creating the procedure?

I've just had a brief read about parameter sniffing - if I understand it correctly its where the parameter values are read during compilation and passed to the query optimiser to see if it can make any further improvements to the execution plan. Would it help if I explicitly convert all the incoming parameter values to the input parameter types in the sproc?

Cheers,

Yonabout

Kristen
Test

22859 Posts

Posted - 2007-05-24 : 11:18:39

"Is it just a matter of dropping and re-creating the procedure?"

Yup, thee is also a Recompile process - from memory its just

EXEC sp_recompile 'MySprocName'

"Would it help if I explicitly convert all the incoming parameter values to the input parameter types in the sproc?"

The normal "trick" to get around this is:

CREATE PROCEDURE MyProc
@MyParam varchar(10)
AS
DECLARE @MyWorkingVariable varchar(10)

SET @MyWorkingVariable = @MyParam

SELECT *
FROM MyTable
WHERE MyColumn = @MyWorkingVariable

"So have you got any ideas how I can find out whats making it go so slow?"

I would compare the Query Plans of Ad-Hoc and Sproc and see HOW they are different. That might shed some light on it; its certainly a good place to start.


SET SHOWPLAN_TEXT ON
GO

-- ... put query here - e.g.:

SELECT * FROM Northwind.dbo.Products

SET SET SHOWPLAN_TEXT OFF
GO

Kristen

yonabout
Posting Yak Master

112 Posts

Posted - 2007-05-30 : 06:10:32

Hi,

Just thought I'd let you know how I got on with this:

I tried everything that you suggested, then spent a few hours with another developer systematically taking it apart and putting it back together.

One thing we noticed was that if we ran the SQL that makes up the stored procedure using parameters on the "caseend" date (like this)

between @startdate and @enddate

, the sql executed quickly. If we tried using string dates (like this)

between '20070401' and '20070501'

, it ran really slow.

Illogical as it sounds, its like the date parameters passed into the sproc somehow lost being datetimes and turned into varchars??

Ultimately, the only solution that I found was to create a #table in my sproc, slelect everything from the view, then perform the selections on the #table instead of the view.

I find this massively annoying, more because I don't understand why the sproc is happy to select form a #table, but stalls when selecting from the view.

Does anyone know why?

Cheers,

Yonabout

Kristen
Test

22859 Posts

Posted - 2007-05-30 : 13:58:42

One thought:

instead of

between '20070401' and '20070501'

try

between CONVERT(datetime, '20070401') and CONVERT(datetime, '20070501')

there have been some threads about Implicit Conversion wrecking performance of some queries.

Kristen

yonabout
Posting Yak Master

112 Posts

Posted - 2007-06-04 : 04:57:22

I gave that one a try, but no joy I'm afraid.

I tried explicitly converting all of the input values to their specific datatypes etc.

The only thing that works is selecting everything from the view into a temporary table, then selecting from the data in the temporary table.

I just don't understand why..

Cheers,

Yonabout

Kristen
Test

22859 Posts

Posted - 2007-06-05 : 04:54:57

Well, with the abstraction of a VIEW your only choice is probably to work carefully through t eh Query Plan and see what it is up to.

You can try making a hand coded query, based on the View but using the Tables direct instead, and adjusting that until you get optimal performance. That will suggest what needs to happen.

There might be indexes that are needed, or some temporary tables - e.g. store the PKs in a TempTable, and then join that back to the actual database tables to get the final data. (This shouldn;t be necessary of course, but I've found that it can help in certain circumstances where the Optimiser is making a poor choice, or Parallelism is getting in a muddle)

Kristen

yonabout
Posting Yak Master

112 Posts

Posted - 2007-06-05 : 05:05:49

OK,

I'll keep trying stuff. If I still can't improve the performance, at least I've got a workaround I can use.

Thanks for all your help!

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources