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
198 13 18 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

1 answer: 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

(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:

x1105
x304
x6

asked: Jul 08 at 02:35 AM

Seen: 38 times

Last Updated: Jul 10 at 09:37 AM

Copyright 2017 Redgate Software. Privacy Policy