Query Takes a Long Time to Run

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
 
G

Guest

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
 
J

James

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
 
M

Mick Walker

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
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....
 
G

Guest

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
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....
 
B

bruce barker

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top