Hello Experts!
I use this same code for two other Datagrids and I don't have this problem so I figure it's something with the select statement I'm using. Every page load is taking around 15 seconds. When I first load the page, edit, cancel, update, change to page 2. All of it is taking a lot longer than it used to. I'm not sure what I changed that did this. Maybe the focus(), but I'm not sure. Hopefully someone can spot what's causing the slow down.
Here is the code:
<%@ Page Language="VB" Debug="true" Explicit="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Sql
<%@ import Namespace="System.DateTime
<script runat="server">
' TODO: update the ConnectionString and Command values for your application
Dim ConnectionString As String = "server=sqlserver;uid=uid;
Dim SelectCommand1 As String = "SELECT Distinct [First Name], [Last Name], [Document Date], Reference#, Control#, Acct#, Description, SumAmt, CommentBox, ControlNo, DateDiff(""d"",GetDate(),"
Dim SelectCommand2 As String = "SELECT distinct * FROM Comments1130"
Dim isEditing As Boolean = False
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
' Databind the data grid on the first request only
' (on postback, bind only in editing, paging and sorting commands)
Yeah()
End If
End Sub
' --------------------------
'
' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
'
Sub DataGrid_ItemCommand(Sende
' this event fires prior to all of the other commands
' use it to provide a more graceful transition out of edit mode
'CheckIsEditing(e.CommandN
End Sub
Sub CheckIsEditing(commandName
If DataGrid1.EditItemIndex <> -1 Then
' we are currently editing a row
If commandName = " Cancel " Then
' user's edit changes (If any) will not be committed
Message.Text = "Your changes have not been saved yet. Please press update to save your changes, or cancel to discard your changes, before selecting another item."
isEditing = True
Else If commandName = " Update" Then
Message.Text = "Your changes have been made."
isEditing = True
End If
End If
End Sub
Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
' turn on editing for the selected row
Dim CommentBox As Textbox
DataGrid1.EditItemIndex = e.Item.ItemIndex
Dim scriptJs As String
BindGrid()
CommentBox = CType(DataGrid1.Items(Data
scriptJs = "<script language=javascript>" & vbCrLf
scriptJs &= "document.getElementById('
scriptJs &= "document.getElementById('
scriptJs &= "<" & "/script>"
If (Not Me.IsStartupScriptRegister
Me.RegisterStartupScript("
End If
End Sub
Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
' update the database with the new values
' get the edit text boxes
Dim Control As String = CType(e.Item.Cells(0).Cont
Dim CommentBox As String = CType(e.Item.Cells(7).Cont
' TODO: update the Command value for your application
Dim myConnection As New SqlConnection(ConnectionSt
Dim UpdateCommand As SqlCommand = new SqlCommand()
UpdateCommand.Connection = myConnection
If AddingNew = True Then
UpdateCommand.CommandText = "INSERT INTO comments1130(ControlNo, CommentBox) VALUES (@Contno, @CommentBox)"
Else
UpdateCommand.CommandText = "UPDATE comments1130 SET CommentBox = @CommentBox WHERE controlNo = @ContNo"
End If
UpdateCommand.Parameters.A
UpdateCommand.Parameters.A
' execute the command
Try
myConnection.Open()
UpdateCommand.ExecuteNonQu
Catch ex as Exception
Message.Text = ex.ToString()
Finally
myConnection.Close()
End Try
' Resort the grid for new records
If AddingNew = True Then
DataGrid1.CurrentPageIndex
AddingNew = false
End If
' rebind the grid
DataGrid1.EditItemIndex = -1
BindGrid()
End Sub
Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
' cancel editing
DataGrid1.EditItemIndex = -1
BindGrid()
AddingNew = False
End Sub
Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventAr
' display a new page of data
If Not isEditing Then
DataGrid1.EditItemIndex = -1
DataGrid1.CurrentPageIndex
BindGrid()
End If
End Sub
Property AddingNew() As Boolean
Get
Dim o As Object = ViewState("AddingNew")
If o Is Nothing Then
Return False
End If
Return CBool(o)
End Get
Set(ByVal Value As Boolean)
ViewState("AddingNew") = Value
End Set
End Property
Sub Yeah()
Const strSQL As String = "usp_SumByControlNo1130"
Dim myConnection As New SqlConnection(ConnectionSt
Dim mycommand = New SqlCommand(strSQL, myConnection)
myConnection.Open()
DataGrid1.DataSource = mycommand.ExecuteReader(Co
BindGrid()
End Sub
Sub BindGrid()
Dim objDataSet As DataSet = New DataSet
Dim myConnection As New SqlConnection(ConnectionSt
Dim Adapter As SqlDataAdapter = New SqlDataAdapter
Adapter.SelectCommand = _
New SqlCommand(SelectCommand1,
myConnection.Open()
Adapter.Fill(objDataSet,"O
Adapter.SelectCommand = _
New SqlCommand(SelectCommand2,
Adapter.Fill(objDataSet,"c
DataGrid1.DataSource = objDataSet
DataGrid1.Databind()
End Sub
</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h4>Bad Checks - 1130 <%=N
</h4>
<hr size="2" />
<form id="form1" runat="server">
<asp:datagrid id="DataGrid1" runat="server" enableviewstate="true" ShowFooter="true" Font-Size="10pt" AutoGenerateColumns="False
<HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></Head
<PagerStyle horizontalalign="Right" backcolor="#C6C3C6" mode="NumericPages" font-size="smaller"></Page
<ItemStyle backcolor="#DEDFDE"></Item
<FooterStyle backcolor="#C6C3C6"></Foot
<Columns>
<asp:BoundColumn DataField="Control#" ReadOnly="False" HeaderText="Cust. Number" />
<asp:BoundColumn DataField="Reference#" ReadOnly="True" HeaderText="Reference#" />
<asp:BoundColumn DataField="Document Date" DataFormatString="{0:d}" ReadOnly="True" HeaderText="Document Date" />
<asp:BoundColumn DataField="Last Name" ReadOnly="True" HeaderText="Last Name" />
<asp:boundColumn DataField="First Name" ReadOnly="True" HeaderText="First Name" />
<asp:BoundColumn DataField="SumAmt" DataFormatString="{0:c}" ReadOnly="True" HeaderText="Amount" />
<asp:BoundColumn DataField="daysold" ReadOnly="True" HeaderText="Days Old" />
<asp:BoundColumn DataField="CommentBox" HeaderText="Comments" ReadOnly="False" />
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit" ItemStyle-Font-Size="small
</Columns>
</asp:datagrid>
<br />
<br />
<br />
<asp:Label id="Message" runat="server" width="80%" forecolor="red" enableviewstate="false"></
</form>
</body>
</html>
Sorry if the code is messy.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I do use a stored procedure. It's used to get sums for me. Otherwise this same code(just different field names and mildly different select statement) works just fine for another grid I use. This one for some reason just takes a while to load. I was hoping it was something obvious that my eyes aren't seeing.
can you debug your code ( because we can not execute this will all of this sql stuff ) and check the time that your code is executed this lines
Adapter.SelectCommand = _
New SqlCommand(SelectCommand1,
myConnection.Open()
Adapter.Fill(objDataSet,"O
and then check this
Adapter.SelectCommand = _
New SqlCommand(SelectCommand2,
Adapter.Fill(objDataSet,"c
that is the that you can understand if the problem is with the sqlstatements
if everything is fine - is the remaining time for display the page is too long
How many records do you display ?
B..G
just make datetime variables that are initialized in the begging of every code block
Dim dt as DateTime = DateTime.Now()
Dim dt1 as DateTime
Dim dt2 as DateTime
Adapter.SelectCommand = _
New SqlCommand(SelectCommand1,
myConnection.Open()
Adapter.Fill(objDataSet,"O
dt1 = DateTime.Now()
Adapter.SelectCommand = _
New SqlCommand(SelectCommand2,
Adapter.Fill(objDataSet,"c
dt2 = DateTime.Now()
and log them into a file or Write them to the page
HTH
B..G
The sproc code:
CREATE procedure usp_SumByControlNo1130
As
BEGIN
INSERT INTO Comments1130 (ControlNo, SumAmt)
SELECT Control#, Sum(Amount)
FROM ODSC_Schedule4 Left Join Comments1130 on ODSC_Schedule4.Control# = Comments1130.ControlNo AND Comments1130.ControlNo Is Null
WHERE odsc_Schedule4.Acct# = '1130' AND NOT EXISTS(SELECT 1 FROM Comments1130 C2 WHERE C2.ControlNo = ODSC_Schedule4.Control# )
GROUP BY Control#
HAVING Sum(Amount) <> 0
IF @@ERROR <> 0
BEGIN
RAISERROR('Insert into Comments1130 failed', 16, 1)
RETURN -1
END
DELETE FROM Comments1130
WHERE 0 = (
SELECT SUM(Sched4.Amount)
FROM ODSC_Schedule4 Sched4
WHERE Sched4.Control# = Comments1130.ControlNo
AND Sched4.Acct# = '1130'
)
RETURN 0
END
GO
I have a table that is meant to have comments written to it from the datagrid. So the stored procedure sums up the "amount" column per each control# in schedule4 table and writes that amount along with the control# to the comments1130 table. That way I can link them up and write comments to the comments table. It should only write the SumAmt and ControlNo if they don't exist in the Comments1130 table.
Hope that makes sense!
That is because you are using a nested ad hoc query, also you are forcing the db engine to do aditional work by using the DISTINCT clause. All that take performance down, and I'm assuming that your db index and primary keys are in right places.
Try to use stored procedures and avoid as much as possible using nested queries.(Also check your indexs a pryjmary keys)
My original guess was that the select statement was slowing me down. I use this sproc on a different datagrid project and it loads up quickly:
CREATE procedure usp_SumByControlNo2
As
BEGIN
INSERT INTO Comments (ControlNo, SumAmt)
SELECT Control#, Sum(Amount)
FROM ODSC_Schedule4 Left Join Comments on ODSC_Schedule4.Control# = Comments.ControlNo Where odsc_schedule4.Acct# = '1020' AND Comments.ControlNo Is Null
GROUP BY Control#
HAVING Sum(Amount) <> 0
IF @@ERROR <> 0
BEGIN
RAISERROR('Insert into Comments failed', 16, 1)
RETURN -1
END
DELETE FROM Comments
WHERE 0 = (
SELECT SUM(Sched4.Amount)
FROM ODSC_Schedule4 Sched4
WHERE Sched4.Control# = Comments.ControlNo
AND Sched4.Acct# = '1020'
)
RETURN 0
END
GO
So why would this other sproc which is almost exactly the same, cause such a decrease in performance?
The stored procedure needs to get the sum of the amounts in schedule4 table for each control# and write that info out to the comments1130 table. Once the sum of amount is 0 it needs to then delete that line in comments1130.
so the control# and sum of "amount" are written to comments1130 where they are either created cause its a new record or overwrites the old control# and SumAmt cause the value has changed.
Does this make sense?
Like I said, I'm a noob. I have no idea what the best approach to this is other than the help I get from here.
You are saying wipe the whole table clean each page load and re-insert the sums? I have more than just those two columns in comments1130. I have another column called "CommentBox". This saves the comments entered by users for each controlno.
sorry
CREATE procedure usp_SumByControlNo1130
As
BEGIN
TRUNCATE TABLE Comments1130
INSERT INTO Comments1130 (ControlNo, SumAmt)
SELECT Control#, Sum(Amount)
FROM ODSC_Schedule4
WHERE odsc_Schedule4.Acct# = '1130'
GROUP BY Control#
HAVING Sum(Amount) <> 0
END
Anyway your stored procedure doesn't return the result set. Then you shouldn't be able to bind it to the datagrid though.
Was there any reason to put it in Comments1130 table? Why don't we just retrieve the value from Schedule4 directly?
Hope it will work....
CREATE procedure usp_SumByControlNo1130
As
BEGIN
UPDATE Comments1130
SET SumAmt = (SELECT SUM(Amount) FROM ODSC_Schedule4 WHERE ODSC_Schedule4.Control#=Co
INSERT INTO Comments1130 (ControlNo, SumAmt)
SELECT Control#, Sum(Amount)
FROM ODSC_Schedule4
LEFT JOIN Comments1130 ON ODSC_Schedule4.Control# = Comments1130.ControlNo AND Comments1130.ControlNo Is Null
WHERE odsc_Schedule4.Acct# = '1130'
GROUP BY Control#
HAVING Sum(Amount) <> 0
DELETE Comments1130 WHERE SumAmt=0
SELECT * FROM Comments1130
END
Don't forget to create indexes on the following fields
ODSC_Schedule4.Control#
Comments1130.ControlNo
odsc_Schedule4.Acct#
by executing the following statement in Query Analyzer
SET NOCOUNT OFF
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'idx_schedule4_control')
DROP INDEX odsc_Schedule4.idx_schedul
GO
CREATE INDEX idx_schedule4_control
ON odsc_Schedule4 (Control#)
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'idx_schedule4_acct')
DROP INDEX odsc_Schedule4.idx_schedul
GO
CREATE INDEX idx_schedule4_acct
ON odsc_Schedule4 (acct#)
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'idx_comment1130_controlno
DROP INDEX comment1130.idx_comment113
GO
CREATE INDEX idx_comment1130_controlno
ON comment1130(ControlNo)
GO
sorry... the stored procedure is not correct.
CREATE procedure usp_SumByControlNo1130
As
BEGIN
UPDATE Comments1130
SET SumAmt = (SELECT SUM(Amount) FROM ODSC_Schedule4 WHERE ODSC_Schedule4.Control#=Co
INSERT INTO Comments1130 (ControlNo, SumAmt)
SELECT Control#, Sum(Amount)
FROM ODSC_Schedule4
LEFT JOIN Comments1130 ON ODSC_Schedule4.Control# = Comments1130.ControlNo AND Comments1130.ControlNo Is Null
WHERE odsc_Schedule4.Acct# = '1130'
GROUP BY Control#
HAVING Sum(Amount) <> 0
DELETE Comments1130 WHERE SumAmt=0
SELECT * FROM Comments1130
END
It is working. The SumAmt is correct. It's giving me the wrong date for each posting though.
So if there are two entries to Schedule4 like so:
8/17/03 2000 454593
12/22/03 -600 454593
Then it was using the more recent date. Now the amounts are correct but its using the original posted date. I think that was the reason for the nested query I was using. Not sure if this is a big problem but it would be nice to have the accurate date as I use it for a "daysold" field.
Don't forget to create indexes on the following fields
ODSC_Schedule4.Control#
Comments1130.ControlNo
odsc_Schedule4.Acct#
by executing the following statement in Query Analyzer
SET NOCOUNT OFF
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'idx_schedule4_control')
DROP INDEX odsc_Schedule4.idx_schedul
GO
CREATE INDEX idx_schedule4_control
ON odsc_Schedule4 (Control#)
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'idx_schedule4_acct')
DROP INDEX odsc_Schedule4.idx_schedul
GO
CREATE INDEX idx_schedule4_acct
ON odsc_Schedule4 (acct#)
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'idx_comment1130_controlno
DROP INDEX comment1130.idx_comment113
GO
CREATE INDEX idx_comment1130_controlno
ON comment1130(ControlNo)
GO
?? You've lost me there. Can I do this inside the sproc or do I need to go in and setup the indexes before I use the new sproc?
Not sure if I've done this right but I ran the indexing code you put up first in the query analzyer and it said: The command(s) completed successfully.
Then I ran the datagrid with the new sproc. The load time was just as slow as before, had the correct amounts, but is still using the wrong dates and stuff.
My bad....
it should be
INSERT INTO Comments1130 (ControlNo, SumAmt)
SELECT Control#, Sum(Amount)
FROM ODSC_Schedule4
LEFT JOIN Comments1130 ON ODSC_Schedule4.Control# = Comments1130.ControlNo AND Comments1130.SumAmt Is Null
WHERE odsc_Schedule4.Acct# = '1130'
GROUP BY Control#
HAVING Sum(Amount) <> 0
you change this from
Dim SelectCommand1 As String = "SELECT Distinct [First Name], [Last Name], [Document Date], Reference#, Control#, Acct#, Description, SumAmt, CommentBox, ControlNo, DateDiff(""d"",GetDate(),"
to
Dim SelectCommand1 As String = "SELECT [First Name], [Last Name], [Document Date], Reference#, o.Control#, Acct#, Description, SumAmt, CommentBox, ControlNo, DateDiff(""d"",GetDate(),[
DateDiff(""d"",GetDate(),[
odsc_schedule4 o
INNER JOIN
(SELECT Control#, MAX(AutoID) FROM ODSC_Schedule4 WHERE o.Acct# = '1130' GROUP BY Control#) a
ON
a.Control# = o.Control# AND a.AutoID = o.AutoID AND o.Acct# = '1130'
INNER JOIN
Comments1130 c
ON
o.Control# = c.ControlNo
Inner Join odsc_names on [Name ID] = ControlNo
"
remove all new lines stuff..... I wrapped it to make it easier to read.
testn: The new select statement is crazy. Are you asigning alias's to it? I'm guessing that's what the o's and a's are for? I've never done this so I'd like to understand it.
I get this error when I load the page:
Exception Details: System.Data.SqlClient.SqlE
Source Error:
Line 189: New SqlCommand(SelectCommand1,
Line 190: myConnection.Open()
Line 191: Adapter.Fill(objDataSet,"O
Line 192:
Line 193:
Source File: c:\inetpub\wwwroot\ASPX\Ne
The stored procedure is still recreating each record every time it's executed.
Maybe I screwed it up somehow:
CREATE procedure usp_SumByControlNo1130
As
BEGIN
Update Comments1130
Set SumAmt = (Select Sum(Amount) From Odsc_Schedule4 Where Odsc_schedule4.Control# = Comments1130.ControlNo And Odsc_Schedule4.Acct# = '1130')
INSERT INTO Comments1130 (ControlNo, SumAmt)
SELECT Control#, Sum(Amount)
FROM ODSC_Schedule4
LEFT JOIN Comments1130 ON ODSC_Schedule4.Control# = Comments1130.ControlNo AND Comments1130.SumAmt Is Null
WHERE odsc_Schedule4.Acct# = '1130'
GROUP BY Control#
HAVING Sum(Amount) <> 0
DELETE Comments1130 WHERE SumAmt=0
END
GO
Business Accounts
Answer for Membership
by: MattWarePosted on 2004-01-08 at 11:05:39ID: 10073958
Why don't you use stored procedures?