Query Takes a Long Time to Run

  • Thread starter Thread starter Mick Walker
  • Start date Start date
M

Mick Walker

I am in the process of writing a web application using ASP.NET. I am
Using a SqlDataSource to populate the contents of a Repeater control.

To me the select statement appears to be very very simple, it is:

SELECT [UID], [SupplierSKUCode], [Date_Imported], [Name], [Import_UID]
FROM [Import_Lines] WHERE (([Import_UID] = @Import_UID) AND
([LinkedSKUUID] IS NULL)) ORDER BY [Date_Imported]

Yet it takes 5 Mins plus to bind the data source to the repeater
control. The statement will only ever return < 1500 results due to the
database logic within the DB.
I simply cant understand why it would take so long to bind these items
to a repeater. My Repeater is defined as follows:

<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate><table cellpadding="5"
cellspacing="2" width="90%"><tr class="TableCaptions">
<td>Supplier SKU Code</td>
<td>Date Imported</td>
<td>Name</td>
<td>Manual SKU Input</td>
<td>Ignore</td>
<tr />
</HeaderTemplate>
<ItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
Skin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />
</td>
<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList><asp:TextBox
ID="txtLineID" runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
SKin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />

</td>

<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList>
<asp:TextBox ID="txtLineID"
runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</AlternatingItemTemplate>
</asp:Repeater>

I set the repeaters datasource programatically through my code, to point
to my SqlDataSource.

Does anyone know why it taked to long to DataBind() to the repeater control?

Kind Regards
Mick Walker
 
Mick,
I am not sure its a good idea to assume that the databinding portion of your
logic is where the bottleneck is. It could be the execution of the SQL and
bringing back the results.
So in order to really get a handle on it, you'll need to pick apart the
different portions of the process and do some timings on each.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com



Mick Walker said:
I am in the process of writing a web application using ASP.NET. I am
Using a SqlDataSource to populate the contents of a Repeater control.

To me the select statement appears to be very very simple, it is:

SELECT [UID], [SupplierSKUCode], [Date_Imported], [Name], [Import_UID]
FROM [Import_Lines] WHERE (([Import_UID] = @Import_UID) AND
([LinkedSKUUID] IS NULL)) ORDER BY [Date_Imported]

Yet it takes 5 Mins plus to bind the data source to the repeater
control. The statement will only ever return < 1500 results due to the
database logic within the DB.
I simply cant understand why it would take so long to bind these items
to a repeater. My Repeater is defined as follows:

<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate><table cellpadding="5"
cellspacing="2" width="90%"><tr class="TableCaptions">
<td>Supplier SKU Code</td>
<td>Date Imported</td>
<td>Name</td>
<td>Manual SKU Input</td>
<td>Ignore</td>
<tr />
</HeaderTemplate>
<ItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
Skin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />
</td>
<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList><asp:TextBox
ID="txtLineID" runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
SKin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />

</td>

<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList>
<asp:TextBox ID="txtLineID"
runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</AlternatingItemTemplate>
</asp:Repeater>

I set the repeaters datasource programatically through my code, to point
to my SqlDataSource.

Does anyone know why it taked to long to DataBind() to the repeater control?

Kind Regards
Mick Walker
Click to expand...
 
Are you 100% certain the query itself is taking that long? Gut feeling is
there's some code/logic involved with the bind or the itemdatabound event
that's causing it to be so slow. Have you tried writing out the query and
running it in Query Analyzer? If so, how long does it take there?

Mick Walker said:
I am in the process of writing a web application using ASP.NET. I am Using
a SqlDataSource to populate the contents of a Repeater control.

To me the select statement appears to be very very simple, it is:

SELECT [UID], [SupplierSKUCode], [Date_Imported], [Name], [Import_UID]
FROM [Import_Lines] WHERE (([Import_UID] = @Import_UID) AND
([LinkedSKUUID] IS NULL)) ORDER BY [Date_Imported]

Yet it takes 5 Mins plus to bind the data source to the repeater control.
The statement will only ever return < 1500 results due to the database
logic within the DB.
I simply cant understand why it would take so long to bind these items to
a repeater. My Repeater is defined as follows:

<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate><table cellpadding="5" cellspacing="2"
width="90%"><tr class="TableCaptions">
<td>Supplier SKU Code</td>
<td>Date Imported</td>
<td>Name</td>
<td>Manual SKU Input</td>
<td>Ignore</td>
<tr />
</HeaderTemplate>
<ItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
Skin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />
</td>
<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList><asp:TextBox
ID="txtLineID" runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
SKin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx"
/>

</td>

<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList>
<asp:TextBox ID="txtLineID" runat="server"
Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</AlternatingItemTemplate>
</asp:Repeater>

I set the repeaters datasource programatically through my code, to point
to my SqlDataSource.

Does anyone know why it taked to long to DataBind() to the repeater
control?

Kind Regards
Mick Walker
Click to expand...
 
Peter said:
Mick,
I am not sure its a good idea to assume that the databinding portion of your
logic is where the bottleneck is. It could be the execution of the SQL and
bringing back the results.
So in order to really get a handle on it, you'll need to pick apart the
different portions of the process and do some timings on each.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com



Mick Walker said:
I am in the process of writing a web application using ASP.NET. I am
Using a SqlDataSource to populate the contents of a Repeater control.

To me the select statement appears to be very very simple, it is:

SELECT [UID], [SupplierSKUCode], [Date_Imported], [Name], [Import_UID]
FROM [Import_Lines] WHERE (([Import_UID] = @Import_UID) AND
([LinkedSKUUID] IS NULL)) ORDER BY [Date_Imported]

Yet it takes 5 Mins plus to bind the data source to the repeater
control. The statement will only ever return < 1500 results due to the
database logic within the DB.
I simply cant understand why it would take so long to bind these items
to a repeater. My Repeater is defined as follows:

<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate><table cellpadding="5"
cellspacing="2" width="90%"><tr class="TableCaptions">
<td>Supplier SKU Code</td>
<td>Date Imported</td>
<td>Name</td>
<td>Manual SKU Input</td>
<td>Ignore</td>
<tr />
</HeaderTemplate>
<ItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
Skin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />
</td>
<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList><asp:TextBox
ID="txtLineID" runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
SKin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />

</td>

<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList>
<asp:TextBox ID="txtLineID"
runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</AlternatingItemTemplate>
</asp:Repeater>

I set the repeaters datasource programatically through my code, to point
to my SqlDataSource.

Does anyone know why it taked to long to DataBind() to the repeater control?

Kind Regards
Mick Walker
Click to expand...
Click to expand...
Hi Peter,

Thats the thing, the SQL Server is running local, I have ran the query
directly in the management studio, and it returns results instantly.

I am really at a loss with this one....
 
Running the query in SQL Management studio is not exactly the same mechanism
as having it run via a control in the page. Take a look at th Repeater's
databinding events, where you can implement some timing and testing logic.
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com



Mick Walker said:
Peter said:
Mick,
I am not sure its a good idea to assume that the databinding portion of your
logic is where the bottleneck is. It could be the execution of the SQL and
bringing back the results.
So in order to really get a handle on it, you'll need to pick apart the
different portions of the process and do some timings on each.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com



Mick Walker said:
I am in the process of writing a web application using ASP.NET. I am
Using a SqlDataSource to populate the contents of a Repeater control.

To me the select statement appears to be very very simple, it is:

SELECT [UID], [SupplierSKUCode], [Date_Imported], [Name], [Import_UID]
FROM [Import_Lines] WHERE (([Import_UID] = @Import_UID) AND
([LinkedSKUUID] IS NULL)) ORDER BY [Date_Imported]

Yet it takes 5 Mins plus to bind the data source to the repeater
control. The statement will only ever return < 1500 results due to the
database logic within the DB.
I simply cant understand why it would take so long to bind these items
to a repeater. My Repeater is defined as follows:

<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate><table cellpadding="5"
cellspacing="2" width="90%"><tr class="TableCaptions">
<td>Supplier SKU Code</td>
<td>Date Imported</td>
<td>Name</td>
<td>Manual SKU Input</td>
<td>Ignore</td>
<tr />
</HeaderTemplate>
<ItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
Skin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />
</td>
<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList><asp:TextBox
ID="txtLineID" runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
SKin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />

</td>

<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList>
<asp:TextBox ID="txtLineID"
runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</AlternatingItemTemplate>
</asp:Repeater>

I set the repeaters datasource programatically through my code, to point
to my SqlDataSource.

Does anyone know why it taked to long to DataBind() to the repeater control?

Kind Regards
Mick Walker
Click to expand...
Click to expand...
Hi Peter,

Thats the thing, the SQL Server is running local, I have ran the query
directly in the management studio, and it returns results instantly.

I am really at a loss with this one....
Click to expand...
 
1500 table rows is too many for the browser anyway (it may take minutes
to render). you should switch to paging and get the row count to 10-20.

-- bruce (sqlwork.com)

Mick said:
I am in the process of writing a web application using ASP.NET. I am
Using a SqlDataSource to populate the contents of a Repeater control.

To me the select statement appears to be very very simple, it is:

SELECT [UID], [SupplierSKUCode], [Date_Imported], [Name], [Import_UID]
FROM [Import_Lines] WHERE (([Import_UID] = @Import_UID) AND
([LinkedSKUUID] IS NULL)) ORDER BY [Date_Imported]

Yet it takes 5 Mins plus to bind the data source to the repeater
control. The statement will only ever return < 1500 results due to the
database logic within the DB.
I simply cant understand why it would take so long to bind these items
to a repeater. My Repeater is defined as follows:

<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate><table cellpadding="5"
cellspacing="2" width="90%"><tr class="TableCaptions">
<td>Supplier SKU Code</td>
<td>Date Imported</td>
<td>Name</td>
<td>Manual SKU Input</td>
<td>Ignore</td>
<tr />
</HeaderTemplate>
<ItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
Skin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />
</td>
<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList><asp:TextBox
ID="txtLineID" runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr class="AlternatingRowStyle">
<td align="left" valign="top"><asp:Label
ID="lblSupSKU" runat="server" Text='<%# Eval("SupplierSKUCode")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblDate" runat="server" Text='<%# Eval("Date_Imported")
%>'></asp:Label></td>
<td align="left" valign="top"><asp:Label
ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label></td>
<td align="left" valign="top">
<radC:RadComboBox
ID="RadComboSKU"
runat="server"
SKin="WindowsXP"
MarkFirstMatch="True"
Height="150px"
EnableLoadOnDemand="True"
ExternalCallBackPage="ComboStreamer.aspx" />

</td>

<td align="left" valign="top">
<asp:RadioButtonList ID="RBIgnore"
runat="server">
<asp:ListItem>Ignore
Once</asp:ListItem>
<asp:ListItem>Ignore
Forever</asp:ListItem>
</asp:RadioButtonList>
<asp:TextBox ID="txtLineID"
runat="server" Text='<%# Eval("UID")%>'></asp:TextBox>
</td>
</tr>
</AlternatingItemTemplate>
</asp:Repeater>

I set the repeaters datasource programatically through my code, to point
to my SqlDataSource.

Does anyone know why it taked to long to DataBind() to the repeater
control?

Kind Regards
Mick Walker
Click to expand...
 
Back
Top