IIS slowing down when executing SQL querries

Navicerts
Navicerts used Ask the Experts™
on
Hi everyone !

Since I've installed SQL Server 2005 SP4 (which I can't uninstall), I'm getting into troubles when running some querries through ASP.NET pages (it takes 10 seconds to run), whereas, if I run the same query directly from SQL Server Management Studio, it works perfectly (less than 1 second).

Here is the configuration :
- Windows 2003 Server R2 SP2 - 4Gb RAM - 32 bits
- IIS 6.0
- SQL Server SP4 (9.0.5057)
- My IIS services is used to run 2 websites on it

I've checked many things to solve this issue :
- connections being closed properly into the code
- users accounts and rights (under IIS and SQL Server)
- web.config file settings
- SQL Server settings

The 2 websites are used daily by a various number of people, and before the SP4 update, everything was working fine.

Sometimes this low performance issue happens, sometimes not, and I'm pretty sure that IIS is the cause of this.

I've used the following script to look at the number of transactions created each time I launch of querry from a page, and sometimes it seems to create up to 30 transactions with the same content.

SELECT 
         SessionID = s.Session_id,
         resource_type,   
         DatabaseName = DB_NAME(resource_database_id),
         request_mode,
         request_type,
         login_time,
         host_name,
         program_name,
         client_interface_name,
         login_name,
         nt_domain,
         nt_user_name,
         s.status,
         last_request_start_time,
         last_request_end_time,
         s.logical_reads,
         s.reads,
         request_status,
         request_owner_type,
         objectid,
         dbid,
         a.number,
         a.encrypted ,
         a.blocking_session_id,
         a.text       
     FROM   
         sys.dm_tran_locks l
         JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
         LEFT JOIN   
         (
             SELECT  *
             FROM    sys.dm_exec_requests r
             CROSS APPLY sys.dm_exec_sql_text(sql_handle)
         ) a ON s.session_id = a.session_id
     WHERE  
         s.session_id > 50

Open in new window


I'm running out of ideas, any help would be greatly appreciated :-)

Thanks in advance !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I had a very similar problem, which was solved by rebuilding all the indexes on my database.
Can you try that please as a first step?

Author

Commented:
Hi !

I've already tried this, using the following script :

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name+']' AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Open in new window


...but with no positive result :(
Okay.
Is it just one query that's running slowly, or all queries?

There are a number of other people experiencing a similar issue, but none are posting enough details to fully compare with you:
http://www.sqlservercentral.com/Forums/Topic1042827-360-1.aspx

Microsoft have also released an update for SP4 where DATEDIFF causes queries to run slowly:
http://support.microsoft.com/kb/2489409
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
Not all of the querries run slowly, and depending on the moment (and maybe the number of active connections on the network to the server ?), none of them are slow.

I've already installed this cumulative update package.

Maybe I've to increase the connection limit on my IIS service ? (I don't know how to do it)

Thanks for your help
Can you post the SQL/ASP.NET code for us to check out?  What IIS/Win are you on?

Author

Commented:
I've another issue that is probably linked to the main problem : I've developped application on handheld devices, and now, when I try to send information using RDA (Remote Data Access), it crashes very often, telling me this : "Either the computer running IIS is out of memory or an incorrect Session ID was sent in a request." (SQLCE error 28003).

So I think the solution of all this is within IIS settings.

Commented:
<<I've used the following script to look at the number of transactions created each time I launch of querry from a page, and sometimes it seems to create up to 30 transactions with the same content.>>
Is the app creating a transaction in a cursor of 30 records to get the detail for each record?

Author

Commented:
The server runs Windows 2003 R2 SP2 with IIS 6.0

Here is one function called on one of the page :

Dim connectionString As String = "server='*.*.*.*'; trusted_connection=true; database='************'"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
        Dim queryString As String
    
        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
    
        
        
        If (CB_House.Checked = False) Then
        
            queryString = "SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between @DateLowIn and @DateHighIn AND Farm=@LocationIDIn	GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])"
    
    
            If (CB_All.Checked = False) Then
                queryString = "SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between @DateLowIn and @DateHighIn AND Farm=@LocationIDIn	And [Line Number]=@Line GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])"
                Dim dbParam_Line As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
                dbParam_Line.ParameterName = "@Line"
                dbParam_Line.Value = LineList.SelectedItem.Text.Trim
                dbParam_Line.DbType = System.Data.DbType.String
                dbCommand.Parameters.Add(dbParam_Line)
            End If
        Else
            queryString = "SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between @DateLowIn and @DateHighIn AND Farm=@LocationIDIn	and House=@House GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])"
    
    
            If (CB_All.Checked = False) Then
                queryString = "SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between @DateLowIn and @DateHighIn AND Farm=@LocationIDIn	And [Line Number]=@Line and House=@House GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])"
                Dim dbParam_Line As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
                dbParam_Line.ParameterName = "@Line"
                dbParam_Line.Value = LineList.SelectedItem.Text.Trim
                dbParam_Line.DbType = System.Data.DbType.String
                dbCommand.Parameters.Add(dbParam_Line)
            End If
            
            
            Dim dbParam_House As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
            dbParam_House.ParameterName = "@House"
            dbParam_House.Value = HouseList.SelectedItem.Text.Trim
            dbParam_House.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_House)
        End If
    
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection
        dbConnection.Open()
        
        dbCommand.CommandTimeout = 0
    
        Dim dbParam_LocationIDIn As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_LocationIDIn.ParameterName = "@LocationIDIn"
        dbParam_LocationIDIn.Value = FarmList.SelectedItem.Text.Trim
        dbParam_LocationIDIn.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_LocationIDIn)
    
        Dim dbParam_DateLowIn As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_DateLowIn.ParameterName = "@DateLowIn"
        dbParam_DateLowIn.Value = DateLowIn.Text.Substring(3, 2) & "/" & DateLowIn.Text.Substring(0, 2) & "/" & DateLowIn.Text.Substring(6, 4)
        dbParam_DateLowIn.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_DateLowIn)
    
        Dim dbParam_DateHighIn As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_DateHighIn.ParameterName = "@DateHighIn"
        dbParam_DateHighIn.Value = DateHighIn.Text.Substring(3, 2) & "/" & DateHighIn.Text.Substring(0, 2) & "/" & DateHighIn.Text.Substring(6, 4)
        dbParam_DateHighIn.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_DateHighIn)
    
        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)
        dbCommand.Dispose()
        dbConnection.Close()
        
        Return dataSet

Open in new window

In your code you are creating an IDataParameter object as an instance of SqlParameter.  Why is this please?  The SqlDbType and DbType enumerator values are not the same (different type mappings), and I have fallen foul of this in the past.

Author

Commented:
Jogos : to be more clear, I attached screenshots to this message (result of the query)
Presse-papiers-2.jpg
Presse-papiers-4.jpg

Author

Commented:
Well, to be honnest, I didn't created all the pages' content, so I don't now why the previous programmer choose this solution. But, that was doing the job, as expected.
When your page runs, can you please identify which of the SQL queries is being executed, and post that SQL statement in isolation please?

Author

Commented:
SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between '04/02/2012' and '04/02/2012' AND Farm='Clairiere'	And [Line Number]='A1' GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])

Open in new window


This SQL statement only returns 1 row
I've posted a request for some SQL experts to cast their opinion on this.  Hopefully it won't take too long.  In the meantime, how many times can your function be called in the ASP.NET page?  Is there any chance that the function can be called repeatedly, e.g. from within a loop?

Author

Commented:
The function is called only one time by clicking on a button, there's no loop or anything of the same kind on this page.
Top Expert 2012

Commented:
Just to be clear you are running IIS and SQL Server on the same box?

Author

Commented:
On the same box ? If by box, you mean server, then it's yes !
Top Expert 2012

Commented:
On the same box ? If by box, you mean server, then it's yes !
Unfortunately, that is not a recommended configuration, so your results are never going to be optimum as SQL Server and IIS will always be competing for resources.  You can reduce the Max memory used by SQL Server and this may alleviate somewhat your problems, but in a sense all you are achieving with that is to cripple SQL Server.  You can also make sure that all your queries are optimized.  But in the end these are bandaids and until you are prepared to install SQL Server standalone you are going to be plagued with problems.

I am sorry for the bad news.

Author

Commented:
SQL Server is using half of the system memory (2Gb/4Gb), is it too much ?

I understand your advice, but, what I don't understand is that everything was working really well before the SQL Server 2005 SP4 Update. The problem is that the server which runs SQL Server and IIS is a production server and, therefore, can't be shut down.

There must be a setting that has been changed during the update.
Top Expert 2012

Commented:
SQL Server is using half of the system memory (2Gb/4Gb), is it too much ?
It is a 32-bit system, so unless you have it configured to use AWE it cannot use much more.

I feel your pain, but I don't believe this has anything to do with SP4, that was merely coincidental.  If you feel strongly about it, try ininstalling SQL Server and then re-installing up to SP3, but make sure you have someone knowledgeable about SQL Server to help you.

Author

Commented:
Unfortunately, I can't restore everything on another server for the moment. I've to find a solution avoiding this option.

Anyway, thanks for your support.
Top Expert 2012

Commented:
I've to find a solution avoiding this option.
Since you are looking for a bandaid:
Have you verified that the queries that are taking a long time are optimized?
Have you considered rewriting the queries?
How many rows are returned in a query that is taking a long time?
Please fire up SQL Profiler and get it running while ASP executes the 'slow' query.  When its done, can you paste that query here (so it will contain the full code plus parameter values)?

Also can you paste that actual code into SSMS and run it, and test execution times, and also post a screen grab of the execution plan?

Author

Commented:
Through the ASP page, it takes 10 seconds to return 1 row.
If I execute the same query under SSMS, it takes less than second.

Here is the corresponding query :

SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between '04/02/2012' and '04/02/2012' AND Farm='Clairiere' and [Line Number]='A1'	 GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])

Open in new window


I think queries are pretty well optimized.
Profiler-Trace-Log.jpg
Execution-Plan.doc
@acperkins can you see anything weird there?  It looks okay to me (formatted code below):

SELECT	
	[Farm] as Ferme,	
	[House] as Bâtiment,	
	[Line Number] as Souche,	
	Min([Cage Pen Number]) As [Cage Min],
	Max([Cage Pen Number]) As [Cage Max],	 
	Convert(nvarchar, Date, 103) as Date, 	
	Sum([Eggs]) As Oeufs,	
	Sum([Birds]) As Animaux, 
	[Collection Days] as Jours, 
	Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] 
FROM	 
	[Egg Summary (Multiple Cage)] 
WHERE 
	Date between '04/02/2012' and '04/02/2012' AND 
	Farm='Clairiere' and 
	[Line Number]='A1'	 
GROUP BY 
	[Farm], 
	[House],	
	[Line Number], 
	[Collection Days],	
	[Location ID], 	
	Convert(nvarchar, Date, 103), 
	Datepart(yyyy,[Date]), 
	Datepart(mm,[Date]), 
	Datepart(dd,[Date]) 
ORDER BY 
	Farm, 
	House, 
	[Line Number], 
	Datepart(yyyy,[Date]),
	Datepart(mm,[Date]),
	Datepart(dd,[Date])

Open in new window


@Navicerts can you now please post the corresponding ASP.NET code that first creates the SQL objects and initiates the SQL code?  I want to rewrite that particular branch of logic using SQL-based objects rather than IDb stuff if possible...

Author

Commented:
Rouchie, here is the function that takes informations from the database.

 Function ReturnEggsM() As System.Data.DataSet
        Dim connectionString As String = "server='*.*.*.*'; trusted_connection=true; database='***********'"
        Dim dbConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
        Dim queryString As String
            
        Dim dbCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand
    
               
        If (CB_House.Checked = False) Then
        
            queryString = "SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between @DateLowIn and @DateHighIn AND Farm=@LocationIDIn	GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])"
    
    
            If (CB_All.Checked = False) Then
                queryString = "SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between @DateLowIn and @DateHighIn AND Farm=@LocationIDIn	And [Line Number]=@Line GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])"
                Dim dbParam_Line As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
                dbParam_Line.ParameterName = "@Line"
                dbParam_Line.Value = LineList.SelectedItem.Text.Trim
                dbParam_Line.DbType = System.Data.DbType.String
                dbCommand.Parameters.Add(dbParam_Line)
            End If
        Else
            queryString = "SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between @DateLowIn and @DateHighIn AND Farm=@LocationIDIn	and House=@House GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])"
    
    
            If (CB_All.Checked = False) Then
                queryString = "SELECT	[Farm] as Ferme,	[House] as Bâtiment,	[Line Number] as Souche,	Min([Cage Pen Number]) As [Cage Min],Max([Cage Pen Number]) As [Cage Max],	 Convert(nvarchar, Date, 103) as Date, 	Sum([Eggs]) As Oeufs,	Sum([Birds]) As Animaux, [Collection Days] as Jours, Cast(((cast(SUM(Eggs) as float)/(cast(SUM(Birds) as float)))*100) / [Collection Days] as numeric(5,2)) as [%] FROM	 [Egg Summary (Multiple Cage)] WHERE Date between @DateLowIn and @DateHighIn AND Farm=@LocationIDIn	And [Line Number]=@Line and House=@House GROUP BY [Farm], [House],	[Line Number], [Collection Days],	[Location ID], 	Convert(nvarchar, Date, 103), Datepart(yyyy,[Date]),		Datepart(mm,[Date]),	Datepart(dd,[Date]) ORDER BY Farm, House, [Line Number], Datepart(yyyy,[Date]),	Datepart(mm,[Date]), Datepart(dd,[Date])"
                Dim dbParam_Line As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
                dbParam_Line.ParameterName = "@Line"
                dbParam_Line.Value = LineList.SelectedItem.Text.Trim
                dbParam_Line.DbType = System.Data.DbType.String
                dbCommand.Parameters.Add(dbParam_Line)
            End If
            
            
            Dim dbParam_House As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
            dbParam_House.ParameterName = "@House"
            dbParam_House.Value = HouseList.SelectedItem.Text.Trim
            dbParam_House.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_House)
        End If
    
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection
        dbConnection.Open()
        
        dbCommand.CommandTimeout = 0
    
        Dim dbParam_LocationIDIn As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
        dbParam_LocationIDIn.ParameterName = "@LocationIDIn"
        dbParam_LocationIDIn.Value = FarmList.SelectedItem.Text.Trim
        dbParam_LocationIDIn.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_LocationIDIn)
    
        Dim dbParam_DateLowIn As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
        dbParam_DateLowIn.ParameterName = "@DateLowIn"
        dbParam_DateLowIn.Value = DateLowIn.Text.Substring(3, 2) & "/" & DateLowIn.Text.Substring(0, 2) & "/" & DateLowIn.Text.Substring(6, 4)
        dbParam_DateLowIn.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_DateLowIn)
    
        Dim dbParam_DateHighIn As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
        dbParam_DateHighIn.ParameterName = "@DateHighIn"
        dbParam_DateHighIn.Value = DateHighIn.Text.Substring(3, 2) & "/" & DateHighIn.Text.Substring(0, 2) & "/" & DateHighIn.Text.Substring(6, 4)
        dbParam_DateHighIn.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_DateHighIn)
    
        Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)
        dbCommand.Dispose()
        dbConnection.Close()
        dbConnection.Dispose()
        Return dataSet
    
    End Function

Open in new window

Top Expert 2012
Commented:
I think queries are pretty well optimized.
Do you have an index on Farm, House, [Line Number], [Location ID], [Date]?

Have you considered doing the date and "Collection Days" format in your ASP code?

What are the data types for Eggs birds?

Your WHERE clause may be a special case, but if not there is little case to be made for doing a GROUP BY on Date, Farm, Line Number:  There is only one.

In any case, I would re-write your query as follows (from looking at your WHERE clause I am going to assume your [Date] is a datetime data type and does not have time, it is at midnight):

SELECT  [Farm] AS Ferme,
        [House] AS Bâtiment,
        [Line Number] AS Souche,
        MIN([Cage Pen Number]) AS [Cage Min],
        MAX([Cage Pen Number]) AS [Cage Max],
        CONVERT(varchar(10), [Date], 103) AS Date,
        SUM([Eggs]) AS Oeufs,
        SUM([Birds]) AS Animaux,
        [Collection Days] AS Jours,
        CAST(((CAST(SUM(Eggs) AS float) / (CAST(SUM(Birds) AS float))) * 100) / [Collection Days] AS numeric(5, 2)) AS [%]
FROM    [Egg Summary (Multiple Cage)]
WHERE   [Date] BETWEEN '20120402' AND '20120402'
        AND Farm = 'Clairiere'
        AND [Line Number] = 'A1'
GROUP BY [Farm],
        [House],
        [Line Number],
        [Collection Days],
        [Location ID],
        [Date]
ORDER BY Farm,
        House,
        [Line Number],
        [Date]

Open in new window

Author

Commented:
Problem solved ! =) In fact, it was the formatted date that was sent through the ASP page, which was slowing down everything...

Dim DateLow As String
        Dim DateHigh As String
        
        DateLow = DateLowIn.Text.Substring(6, 4) & DateLowIn.Text.Substring(3, 2) & DateLowIn.Text.Substring(0, 2)
        DateHigh = DateHighIn.Text.Substring(6, 4) & DateHighIn.Text.Substring(3, 2) & DateHighIn.Text.Substring(0, 2)

SQL Statement : "Select  [....] FROM ... WHERE Date between '" & DateLow & "' and '" & DateHigh & "' ... GROUP BY ... ORDER BY"

Open in new window


It's a bit dirty, but a least it works at normal speed !

Thanks a lot to both of you :)
Good news.

You need to really re-write the ASP code to build dates using the New DateTime() constructor to avoid this.  Its clean and correct.
Also, check out System.Data.SqlClient and System.Data.SqlDbType to build your queries.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial