linq to sql speed question

[Ralph]

I created a query in linq to sql it is just a join and taking a long
time to run.
I am logging to a file using the log of the context.
If I take the query there and run it it returns in 17 seconds in query
analyser.
However when running in my app it takes half an hour.
Anyone have any similar experiences?
Also is the query shown in the log the one the actually gets run?

Thanks in advance

Sincerely

Ralph

 

[Gregory A. Beamer]

I created a query in linq to sql it is just a join and taking a long
time to run.
I am logging to a file using the log of the context.
If I take the query there and run it it returns in 17 seconds in query
analyser.
However when running in my app it takes half an hour.

In general, it is either query or connection method that causes speed
problems of this type.

Profile SQL Server to see if anything is strange there. Then examine the
libraries set up on the server and the client. You could have some
issues there.

Also is the query shown in the log the one the actually gets run?

To check, you can use Profiler and see what is being run, but it should
be the same.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Jeroen Mostert]

I created a query in linq to sql it is just a join and taking a long
time to run.
I am logging to a file using the log of the context.
If I take the query there and run it it returns in 17 seconds in query
analyser.

That's already dog slow. How many records does it return?

However when running in my app it takes half an hour.
Anyone have any similar experiences?

Are you certain the database part takes half an hour, or is that for the
LINQ query as a whole (which may involve other operations that don't go to
the database), or even for the query plus any processing you do afterwards?

Are you executing *literally the same query*, or a query with parameters
filled in or rearranged somehow?

It is actually possible for query times to vary wildly from "regular"
applications to when you run them in Query Analyzer/Management Studio, but
this is a fairly uncommon situation that usually has to do with bad
execution plans getting cached. If your database is not too heavily used,
you can try running DBCC FREEPROCCACHE, forcing regeneration of execution
plans. Be careful, on a heavily used server this can cause a significant
(temporary) performance drop. Look into more obvious issues first.

Also is the query shown in the log the one the actually gets run?

This should be the case, yes. When in doubt, though, use SQL Profiler. This
will also allow you to verify if your query is actually taking half an hour,
and if so, why (if you include execution plan events).

 

[Ralph]

That's already dog slow. How many records does it return?


Are you certain the database part takes half an hour, or is that for the
LINQ query as a whole (which may involve other operations that don't go to
the database), or even for the query plus any processing you do afterwards?

Are you executing *literally the same query*, or a query with parameters
filled in or rearranged somehow?

It is actually possible for query times to vary wildly from "regular"
applications to when you run them in Query Analyzer/Management Studio, but
this is a fairly uncommon situation that usually has to do with bad
execution plans getting cached. If your database is not too heavily used,
you can try running DBCC FREEPROCCACHE, forcing regeneration of execution
plans. Be careful, on a heavily used server this can cause a significant
(temporary) performance drop. Look into more obvious issues first.


This should be the case, yes. When in doubt, though, use SQL Profiler. This
will also allow you to verify if your query is actually taking half an hour,
and if so, why (if you include execution plan events).

The one thing I added to the query that all the suddely killed
performance was a join on two nullable types. I added a join between
two tables where both columns I am joinging on are nullable. In my
where clause I specify to only use non null values though.
I'm running profiler now. Although I have a ton of things to sort
through because many other developers are working against this db.

 

[Jeroen Mostert]

Ralph wrote:
[snip]

I'm running profiler now. Although I have a ton of things to sort
through because many other developers are working against this db.

Then by all means do not just profile everything (this is hard on the server
as well), try to filter. If you are using Windows security, you can filter
on your user name. If you are still stuck with SQL security (why?!) you can
try filtering by your local host name. If all else fails you can filter by
part of your query text. Make sure to also include only events that are
relevant ("statement completed" includes execution time and is probably the
only one you need).

 

[Ralph]

Ralph wrote:

[snip]

I'm running profiler now.  Although I have a ton of things to sort
through because many other developers are working against this db.

Then by all means do not just profile everything (this is hard on the server
as well), try to filter. If you are using Windows security, you can filter
on your user name. If you are still stuck with SQL security (why?!) you can
try filtering by your local host name. If all else fails you can filter by
part of your query text. Make sure to also include only events that are
relevant ("statement completed" includes execution time and is probably the
only one you need).

Thanks all for the assistance.
Profiler answered the question. Its actually something happening with
linq that is causeing the time delay with my query running.

 

[Ralph]

Ralph wrote:

[snip]

I'm running profiler now.  Although I have a ton of things to sort
through because many other developers are working against this db.

Then by all means do not just profile everything (this is hard on the server
as well), try to filter. If you are using Windows security, you can filter
on your user name. If you are still stuck with SQL security (why?!) youcan
try filtering by your local host name. If all else fails you can filterby
part of your query text. Make sure to also include only events that are
relevant ("statement completed" includes execution time and is probablythe
only one you need).

Thanks all for the assistance.
Profiler answered the question.  Its actually something happening with
linq that is causeing the time delay with my query running.

Thanks to all who responded.
Turns out that after I figured out profiler and used it properly I
determined that this is indeed a sql problem and nothing to do with
linq. Again thanks for all the assitance.