Definition of Duration in profiler and cartesian cross product

Question

Post reply

Definition of Duration in profiler and cartesian cross product

neil-560592

SSCommitted

Points: 1557
More actions
January 24, 2008 at 6:29 am

#178431

Two questions:
1. In SQL Profiler queries run show the "duration" in milliseconds. Is this the duration in the SQL engine only, or does it also include the time taken to read in the SQL and/or output the results across the network to the client. If the client locks up and refuses to accepts results back leaving the query uncommitted, will it show in profiler as a completed query?
2. I have the following select statement that is called from an application:
SELECT (CASE
WHEN "Title_Grouping_Scheme"."Long_name" Like '%Ad%' THEN
"Order_Row"."First_HJ_depth" * "Order_Row"."HJ_columns"
ELSE
("Size_Card"."Exact_d_distance" / 3.5) * "Size_Card"."Number_of_columns"
END) FROM "Title_Grouping_Scheme" "Title_Grouping_Scheme", "Order_Row" "Order_Row", "Size_Card" "Size_Card"
This query covers three tables Title_Grouping_Scheme, Order_Row, and Size_Card with no join so I would expect it to be a cartesian cross product causing millions of rows to be processed. Yet profiler shows that it only takes between 3 and 9 seconds to run.
Yet If I copy and paste the query from profiler to SQL Server Management Studio and run it it takes 15 minutes + before I end it.
Order_Row has 1000000 rows, and the other two have 400 rows.
Any ideas what's going on here?
http://90.212.51.111 domain

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

It does not include moving results across the network.

Also Select statement does not "commit" it just reads.

Where do you run Profiler? On the server or on the client?

Regards,Yelena Varsha

neil-560592 SSCommitted Points: 1557 More actions · Answer 2

What I mean is what happens if a user runs a select query that returns a lot of data - will the data set be completely cached on the SQL Server until the query completes before it starts to send the data across the network to the client? Or if not does it start sending to the client while the query is running. And in the latter case if the network connection to the client fails half way through, or the client application crashes or otherwise stops accepting the data, will the SQL continue to run (causing data to queue up at the SQL Server waiting to be sent to the client). And in this case will the query show in Profiler as having completed even if the data hasn't all got to the client?

http://90.212.51.111 domain

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

I know that it starts sending to the client while the query is still running, for example try to right-click on the big table, click Open and watch as you already have some results on your client but the query is still running.

This setting depends on how the MIcrosoft Programmers implemented it. Check out these links:

For the previous versions:

http://msdn2.microsoft.com/en-us/library/ms675871(VS.85).aspx

CacheSize Property (ADO)

For .NET based versions:

http://msdn2.microsoft.com/en-us/library/tx1c9c2f.aspx

Paging Through a Query Result (ADO.NET)

I think the query is completed in Profiler when it is completed on the server or cancel request was received by the server.

Regards,Yelena Varsha