Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Speed issue with a view

Author  Topic 

RichardAnderton
Starting Member

15 Posts
Posted - 2011-09-20 : 05:27:20
Hi all,

I have a query that runs against 3 views. It returns data for purchase orders placed on a supplier. Depending on the supplier I want to query, it either takes less than 3 seconds, or over 90 seconds...

The query is of the form:

SELECT poheadm.order_no...
FROM cs3live.dbo.view_combined_stkwhm c, cs3live.dbo.view_combined_podetm podetm, cs3live.dbo.view_combined_poheadm poheadm
WHERE ... AND (poheadm.supplier='200314') AND ...
ORDER BY poheadm.date_required

The query analyser indicates that when the query takes 90 seconds to complete (returning 11 records), it uses an index scan. When it takes 3 seconds to complete (returning more records), it uses a table seek.

The view(s) are UNION ALL views on two identical tables in 2 databases on the same server, using a with (nolock) hint.

Thanks in advance for any help.

Richard

Kristen
Test

22859 Posts
Posted - 2011-09-20 : 06:05:35
Can't really offer much advice without seeing the whole query, and the DDL for the indexes etc.

You might be better off with dynamic SQL if you have a number of scenarios where the query is expect to do Scenario-A or Scenario-B etc.

But the NOLOCK hint is normally a very scary thing to see in production systems - unless you know what the issues are and program around them (and don't use the data, for example, for things that management use to make decisions based on).
Go to Top of Page

RichardAnderton
Starting Member

15 Posts
Posted - 2011-09-20 : 06:14:34
Hi,

The whole query is:

SELECT h.order_no, h.date_entered, d.date_required, d.product, d.description, d.cost_price_u, d.qty_ordered, qty_ordered-qty_received, case h.memo1 when '' then c.description else rtrim(c.description) + ' (' + rtrim(h.memo1) + ')' end as description
FROM view_combined_stkwhm c, view_combined_podetm d, view_combined_poheadm h
WHERE h.order_no = d.order_no AND d.warehouse = c.warehouse AND (h.supplier='200314') AND (qty_ordered-qty_received<>0) AND (h.status<>'9') AND (d.warehouse>='70' And d.warehouse<='70') AND (d.product>='' And d.product<='zzzzz')
ORDER BY h.date_required

This is to return data to an Excel front end which needs to be fast - dirty reads are not an issue as all management reporting is done within the application / onto the tables without a nolock hint.

Thanks

Richard
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-20 : 07:22:59
Is this the where clause for a slow or fast query? would be helpful to see the "other" one.

I imagine that these are for criteria which are user selectable?

	AND (d.warehouse>='70' And d.warehouse<='70') 
	AND (d.product>='' And d.product<='zzzzz')

you might get a tighter query with

AND d.warehouse = '70'

assuming that all d.product fall within the '' to 'zzzzz' range?

And possibly changing

AND (qty_ordered-qty_received<>0)

to

AND (qty_ordered <> qty_received)

will help

These are minor issues though.

Beyond that it will depend what indexes you have. Adding indexes to cover the query is probably the solution you need. SQL can give you help as to what indexes are likely to make a difference. Can't remember in SQL 2005, but in SQL 2008's SSMS if you view the "Actual Query plan" you will get a prompt for suggested additional indexes to add.

The query is likely to perform much better if it is parameterised, rather than having the criteria "in-line". Ask if you not familiar with that and need an example.
Go to Top of Page

RichardAnderton
Starting Member

15 Posts
Posted - 2011-09-20 : 07:55:55
Thank you Kristen.

You correctly identify the user selectable items - though supplier number is also selectable.

The query shown is the fast query. The slow query is identical - it simply uses h.supplier = '200313' instead of h.supplier = '200314'

The slow query returns 11 rows; the fast one 17.

d.warehouse is a potential range - we have numberous warehouses.
Unfortuantely d.product does need restricting.
I will use qty_ordered<>qty_received

If you can give me a clue as to how to parameterise the query, that would be great - at the moment, the SQL statement is created in Excel VBA and attached to a query table on the fly.

Indexes - not so sure on those, and I don't want to mess with the main database / application - those in existance at the moment are:

on stkwhm:
1 - Unique, Non clustered; warehouse
2 - Non clustered; description
3 - Unique, Non clustered; rowid

on podetm:
1 - Unique, Non clustered; Order_no, order_line_no
2 - Non clustered; product
3 - Unique, non clustered; rowid

on poheadm:
1 - Unique, Non clustered; Order_no
2 - Non clustered; Alpha
3 - Non clustered; Supplier
4 - Unique, Non clustered; Rowid

Thanks for any further help.

Richard
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-20 : 08:35:06
"Unfortuantely d.product does need restricting."

Do you mean that other products exist with [product] values OUTSIDE the range '' to 'zzzzz'? Or that some queries DO specify a range?

If it is the second then leaving out the "AND (d.product>='' And d.product<='zzzzz')" when it is "all products" would help (particularly in the parmeterised query shown below). Similarly if the query was for "all warehouses", rather than specifically Warehouse=70 as in this particular query, leaving out the warehouse test would help.

Parameterised query would look like this:

EXEC dbo.sp_ExecuteSQL
N'SELECT	h.order_no,
	h.date_entered,
	d.date_required,
	d.product,
	d.description,
	d.cost_price_u,
	d.qty_ordered,
	qty_ordered-qty_received,
	c.description + COALESCE('' ('' + NullIf(rtrim(h.memo1), '''') + '')'', '''') as description
FROM	view_combined_stkwhm c
	JOIN view_combined_podetm d
		ON d.warehouse = c.warehouse
	JOIN view_combined_poheadm h
		ON h.order_no = d.order_no
WHERE	    (h.supplier=@supplier) 
	AND (qty_ordered <> qty_received) 
	AND (h.status <> @status) 
	AND (d.warehouse >= @warehouse_MIN And d.warehouse <= @warehouse_MAX) 
	AND (d.product >= @product_MIN And d.product <= @product_MAX)
ORDER BY h.date_required',
N'@supplier varchar(99), @status varchar(99), @warehouse_MIN varchar(99), @warehouse_MAX varchar(99), @product_MIN varchar(99), @product_MAX varchar(99)',
@supplier = '200314', @status = '9', @warehouse_MIN = '70', @warehouse_MAX = '70', @product_MIN = '', @product_MAX = 'zzzzz'

I've used "varchar(99)" as an indicator of datatype, you should use the actual datatype / size of the underlying column. If the column is INT be sure to use that rather than VARCHAR as it avoids an implicit data conversion.

You can leave out any white space etc., I've just put it in to make it easier to edit. Note that all single quotes within the SQL statement are "doubled-up"

That query should run "as is" though if you want to try it just like that. The first time it is used the query plan will be cached and that will be used for subsequent runs, which should help their performance. if you can alter it to leave out "un-needed" parts
of the WHERE clause then each variation will be separately cached, with its own optimised query plan.

I don't know what this data is used for once its in Excel, but Excel data tends to get "passed around" and I would be anxious that it is based on queries using NOLOCK.

If you have Reads blocking Writes (which was the most common use of NOLOCK back in SQL 2000) then set the database itself to READ_COMMITTED_SNAPSHOT and you can remove the NOLOCKs - needs checking though!

This particular query (rather than an "Any warehouse" query) would benefit from an index on podetm.warehouse, or alternatively try changing:

	AND (d.warehouse >= 70 And d.warehouse <= 70)

which is referencing the [podetm] table to

	AND (c.warehouse >= 70 And c.warehouse <= 70)

which will reference the [stkwhm] table instead, and is more likely to use the stkwhm.warehouse index (although it could be that the query optimiser will make this optimisation for you, so you may gain nothing, but the query optimiser is not infinitely smart of course !)

You could do with some INCLUDE columns on those indexes, so that they "cover" the query better, then they will be more likely to be used.

Having said that it does depend on what the definition of the VIEWs are, as they may increase the complexity of the query considerably
Go to Top of Page

RichardAnderton
Starting Member

15 Posts
Posted - 2011-09-22 : 05:42:03
Thank you so much for your help. I think I've nearly got it sorted.

I have set it up as a stored proc, and call it from a query table:

.Sql = "exec outstanding_po @supplier='" & SelectedSupplierNo & "', @status=9, @warehouse_MIN='" & lowerwhs & "', @warehouse_MAX='" & upperwhs & "', @product_MIN='" & lowerproductcode & "', @product_MAX='" & upperproductcode & "'"

The speed issue is still perplexing me though... running this from the query window in SQL server, it always runs well (improvement on before) in under a second - though calling it from the Excel query table, and the return time differs wildly - from under a second to over 90 seconds... as before...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-22 : 06:23:16
"from under a second to over 90 seconds... as before..."

Are the times consistent for the same parameter values? or do they vary even for the same parameter values?

Sounds like its a network issue. Do the slower queries return significantly more data? (Rows and columns, or columns that contain much more data - such as long descriptions)
Go to Top of Page

RichardAnderton
Starting Member

15 Posts
Posted - 2011-09-22 : 06:36:29
The quicker query return 28 rows; the longer query returns 11 rows. Not much data - simply an order number, product code, short description, date required, qty, price, etc...

I don't think it's a network issue, as this is the same issue I saw initially - I think it's to do with the query planner using a different plan.

Are the plans stored "forever"? Our servers re-start every night, and I am wondering if the query plan is compiled for the first query run against it - ie if when the first user logs on and runs the query, and it choosed to run 'the slow way' - does it then enforce the slow way for the rest of the day. If that first user submitted a range of values that meant it ran 'the quick way' first, would it then run the quick way all day?

I don't get it!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-22 : 08:48:14
Using a stored procedure the query plan will be cached. They aren't cached "forever" and all cached query plans will be dropped and recreated when you reboot. Query plan may also be thrown away if you specifically ask for the Sproc to be recompiled, or if SQL detects that something relevant has changed (the statistics on the index, for example)

If the first time you run it the query optimiser decides to use Index-X then when SQL uses that query plan, from cache, that index will be used, even if that would be a slower route to resolving the query.

When you were using dynamic SQL before nothing would have been cached (well, it would cache the query plan, but the cached version would only be used if the query was exactly identical). A stored procedure is identical because you have

WHERE MyColumn = @SomeParameter

which does not change, compared to

WHERE MyColumn = 1234
...
WHERE MyColumn = 5678

which is different every time

As you were having variable speeds before I don't think that is the underlying issue.

Are your indexes recently rebuilt, and the statistics recently updated? It would be best to make sure that they are in good shape before doing performance comparisons 'coz if they are stale then the Query Optimiser may be making some lousy choices!
Go to Top of Page

RichardAnderton
Starting Member

15 Posts
Posted - 2011-09-22 : 09:30:02
The database maintenance plan that runs each night updates the stats - last run 9.15 last night, so they're up to date.

I think the issue is that the execution plan was cached for the slow operation this morning.

Is there any way to ensure that the plan is always created the right way? ie using the table seek, rather than the index scan?

Sorry this has taken so long - but I'm learning a lot!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-09-22 : 09:32:30
'Tis why I suggested using dynamic SQL earlier on, as that will only present the field that need to be included in the WHERE clause, and thus each scenario will be separately cached.

The alternative is that your Sprocs calls a child-sproc depending on which parameters are actually relevant. Each child sproc will have its own cached query plan, so each of them will be "efficient" for its own purpose.

You don't need ot have a child sproc for every possible combination, just the main ones should do.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site