| Author |
Topic |
|
shahaab
Starting Member
2 Posts |
 Posted - 2009-10-09 : 14:10:18
|
Hi,When I run SQL Server Profiler I see this weird T-SQL that uses a lot of cpu (905), duration(57003), read(2450) :quote:
declare @p1 int set @p1=180150047 declare @p3 int set @p3=8 declare @p4 int set @p4=1 declare @p5 int set @p5=3 exec sp_cursoropen @p1 output,N'SELECT ID, Name, singerID, albumID, FileName, numofsong,IsNull(text,''y''),(SELECT COALESCE(Nick,(COALESCE(FName + '' '', '' '') + '' '' + COALESCE(LName + '' '', '' '')) + '' '') FROM tblPersons WHERE tblPersons.ID = Songs.SingerID),(SELECT Name FROM Albums WHERE Albums.ID = Songs.AlbumID),Cats,enName FROM Songs WHERE (Name LIKE N''%do mahi%'' OR enName LIKE N''%do mahi%'') AND (Permit = 1) ORDER BY _stat_listen DESC,Hit DESC',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5
I don't know why sql adds all those weird codes before and after my query!I used to run the same query from my ASP page on Windows 2003/SQL 2005 without any problem!Could you guys please give me some insights and help me optimize this query. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-09 : 14:14:05
|
| were you trying to execute a LINQ query and tracing it with profiler? |
 |
|
|
shahaab
Starting Member
2 Posts |
Posted - 2009-10-10 : 00:34:55
|
quote:
Originally posted by visakh16
were you trying to execute a LINQ query and tracing it with profiler?
My original query I run from my ASP page is:quote:
SELECT ID, Name, singerID, albumID, FileName, numofsong,IsNull(text,''y''),(SELECT COALESCE(Nick,(COALESCE(FName + '' '', '' '') + '' '' + COALESCE(LName + '' '', '' '')) + '' '') FROM tblPersons WHERE tblPersons.ID = Songs.SingerID),(SELECT Name FROM Albums WHERE Albums.ID = Songs.AlbumID),Cats,enName FROM Songs WHERE (Name LIKE N''%do mahi%'' OR enName LIKE N''%do mahi%'') AND (Permit = 1) ORDER BY _stat_listen DESC,Hit DESC
I don't know how and why when monitoring SQL transactions with profiler the query is like this:quote:
declare @p1 int set @p1=180150047 declare @p3 int set @p3=8 declare @p4 int set @p4=1 declare @p5 int set @p5=3 exec sp_cursoropen @p1 output,N'SELECT ID, Name, singerID, albumID, FileName, numofsong,IsNull(text,''y''),(SELECT COALESCE(Nick,(COALESCE(FName + '' '', '' '') + '' '' + COALESCE(LName + '' '', '' '')) + '' '') FROM tblPersons WHERE tblPersons.ID = Songs.SingerID),(SELECT Name FROM Albums WHERE Albums.ID = Songs.AlbumID),Cats,enName FROM Songs WHERE (Name LIKE N''%do mahi%'' OR enName LIKE N''%do mahi%'') AND (Permit = 1) ORDER BY _stat_listen DESC,Hit DESC',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5
and it uses a lot of resources and take a long time to complete. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-10 : 10:24:28
|
| It looks like ADO/ADO.Net boilerplate for processing Recordsets/DataSets, particularly for client-side cursors. Check the CursorLocation property, or use a Command object and rewrite your query as a SQL stored procedure. |
|
|
|
|
|
|
SQL Server Profiler shows this weird T-SQL!