x

use diff. n on top n will impact performance?

i use a query like:

select CUSTOMER_ID INTO #mc FROM USR_Customer_VW

select TOP 3500 v.CUSTOMER_ID,x.* from #mc v CROSS APPLY [dbo].[udf_GetMaxDate](v.customer_id) x

GO

the view USR_Customer_VW will return more than 10 thousands, udf is a table value function return many columns. when I use top 1000 or top 2000 it will run less than 1 second. but if I use top 3500 it will run more than 30 seconds. can any professional can help me for the issue?

more ▼

asked Jul 08 at 02:35 AM in Default

avatar image

SSGC
196 13 20 25

Generate the execution plans for the 2 queries, and it should become clear - you're likely hitting a point where SQL decides it's better to execute the query a different way

Jul 10 at 09:37 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

8 answers: sort voted first

If you can provider sample data with some dummy columns then we can help otherwise check this post out. Try forcing the order like suggested on the link and see what happens. https://stackoverflow.com/questions/17567674/cross-apply-performance-difference

more ▼

answered Jul 08 at 09:45 PM

avatar image

mdawini
81 1 4

Thank you for great information.
I have create some code for this kind case to test. Please find file package below.

Aug 04 at 06:13 PM SSGC
(comments are locked)
10|1200 characters needed characters left
testcase.zip (3.2 kB)
more ▼

answered Aug 04 at 11:12 PM

avatar image

SSGC
196 13 20 25

(comments are locked)
10|1200 characters needed characters left

Thank you I will look at this tomorrow afternoon... UK time.

more ▼

answered Aug 04 at 11:18 PM

avatar image

mdawini
81 1 4

(comments are locked)
10|1200 characters needed characters left

I have looked at the script and the only change is on indexing. This should take one second for the whole data-set. Please use the script to recreate the tables and indexes or you can just create indexes only. As an example you should create your indexes making sure the selective columns are included like so

CREATE NONCLUSTERED INDEX [IX_ccommunication_Customer_id] ON [dbo].[CCommunication] ( [customer_id] ASC ) INCLUDE ( [Adddate], [Moddate])

cust.zip (46.3 kB)
more ▼

answered Aug 05 at 06:43 PM

avatar image

mdawini
81 1 4

For this test case are all working great!. For real case, there was many index there, I try do not change too many on index, do we have any thing can do on the code in function and cross apply? In real case there are indexes like: CREATE NONCLUSTERED INDEX [IX_ccommunication_Customer_id] ON [dbo].[CCommunication] ( [Adddate], [Moddate] ASC ) INCLUDE (Customer_id) these index create by somebody, I am not sure it will still work for my code? My other question is we have clustered index(primary key) on customer_id, do we still need non_clustered index on customer_id? Thank you for help!

Aug 07 at 04:34 PM SSGC
(comments are locked)
10|1200 characters needed characters left

You need those non clustered indexes I suggested. The primary key is not going to help you here as the function has other columns and you need a covering index to avoid table scans. If you check on the execution plans for the changes I made there are no index scans. It's performing index seeks on the new indexes. Yes the function can be re written if thus what you want.

more ▼

answered Aug 07 at 04:49 PM

avatar image

mdawini
81 1 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1148
x310
x6

asked: Jul 08 at 02:35 AM

Seen: 132 times

Last Updated: Aug 10 at 01:11 PM

Copyright 2017 Redgate Software. Privacy Policy