|
 |
asp_web_howto thread: [asp_web_howto]Problem
Message #1 by =?iso-8859-1?q?jon=20matt?= <jonmath2000@y...> on Wed, 5 Feb 2003 11:23:42 +0000 (GMT)
|
|
Hi All,
My problem is when I execute a Stored Procedure in Query Analyzer I get result in 4 to 5 seconds and When I execute the same Stored
Procedure via ASP I get result in 10 to 12 minutes.
can anyone explain what I am doing wrong?
Cheers
---------------------------------
With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Message #2 by Mark Eckeard <meckeard2000@y...> on Wed, 5 Feb 2003 05:09:58 -0800 (PST)
|
|
Post your code. It's probably the way you are
connecting to the database, returning the results,
writing them to the browser, etc.
Mark
--- jon matt <jonmath2000@y...> wrote:
>
> Hi All,
>
> My problem is when I execute a Stored Procedure in
> Query Analyzer I get result in 4 to 5 seconds and
> When I execute the same Stored Procedure via ASP I
> get result in 10 to 12 minutes.
>
> can anyone explain what I am doing wrong?
>
> Cheers
>
>
>
>
>
> ---------------------------------
> With Yahoo! Mail you can get a bigger mailbox --
> choose a size that fits your needs
>
>
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Message #3 by =?iso-8859-1?q?jon=20matt?= <jonmath2000@y...> on Wed, 5 Feb 2003 14:32:15 +0000 (GMT)
|
|
Here is the code..
<%@ Language=VBScript %>
<%
Response.Buffer=true
MyWhereClause = request("whereClause") // holds where somefiled=somevalue and other field = someother value
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("MyConnection")
Set rs=Conn.Execute("ProcedureName " & chr(34) & MyWhereClause & chr(34))// call to stored procedure
Do until rs.EOF %>
<tr>
<td><%=trim(rs("name"))%></td>
<td><%=trim(rs("age"))%></td>
<td><%=trim(rs("rollno"))%></td>
</tr>
<%
rs.MoveNext
Loop
rs.close
set rs= nothing
Conn.Close
set Conn = nothing
%>
If I use the same where clause in query analyser to execute stored procedure which is very very quick.
Cheers
---------------------------------
With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Message #4 by Mark Eckeard <meckeard2000@y...> on Wed, 5 Feb 2003 06:54:10 -0800 (PST)
|
|
In most cases, Query Analyzer is always going to be
faster.
One suggestion I would make would be to change your
code where you are looping through the recordset to
display the records.
The way you are doing it now, you are context
switching about 8 times per record. Change it to
something like this:
do until RS.EOF
response.write ("<tr>" & vbcrlf & "<td>"
rs.fields("FieldName") & vbcrlf & "</td>" & vbcrlf &
"</tr>" & vbcrlf)
RS.movenext
Now, this may or may not make much of a difference.
Depends on the number of records. If you were
displaying 200+ records, you could see a difference.
But if it's < 50, you might not.
Mark.
--- jon matt <jonmath2000@y...> wrote:
>
> Here is the code..
> <%@ Language=VBScript %>
> <%
> Response.Buffer=true
> MyWhereClause = request("whereClause") // holds
> where somefiled=somevalue and other field
> someother value
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open Application("MyConnection")
> Set rs=Conn.Execute("ProcedureName " & chr(34) &
> MyWhereClause & chr(34))// call to stored procedure
> Do until rs.EOF %>
> <tr>
> <td><%=trim(rs("name"))%></td>
> <td><%=trim(rs("age"))%></td>
> <td><%=trim(rs("rollno"))%></td>
> </tr>
> <%
> rs.MoveNext
> Loop
> rs.close
> set rs= nothing
> Conn.Close
> set Conn = nothing
> %>
>
>
> If I use the same where clause in query analyser to
> execute stored procedure which is very very quick.
> Cheers
>
>
>
>
> ---------------------------------
> With Yahoo! Mail you can get a bigger mailbox --
> choose a size that fits your needs
>
>
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Message #5 by Imar Spaanjaars <Imar@S...> on Wed, 05 Feb 2003 18:18:50 +0100
|
|
In addition to this, you could use GetRows() to get your data into a two
dimensional array. That will perform a lot faster than using the MoveNext()
method.
Check out the following URL for a tutorial on this.
http://www.aspalliance.com/mbrink1111/getrows/getrows.asp
Cheers,
Imar
At 06:54 AM 2/5/2003 -0800, you wrote:
>In most cases, Query Analyzer is always going to be
>faster.
>
>One suggestion I would make would be to change your
>code where you are looping through the recordset to
>display the records.
>
>The way you are doing it now, you are context
>switching about 8 times per record. Change it to
>something like this:
>
>do until RS.EOF
>
>response.write ("<tr>" & vbcrlf & "<td>"
>rs.fields("FieldName") & vbcrlf & "</td>" & vbcrlf &
>"</tr>" & vbcrlf)
>
>RS.movenext
>
>Now, this may or may not make much of a difference.
>Depends on the number of records. If you were
>displaying 200+ records, you could see a difference.
>But if it's < 50, you might not.
>
>Mark.
>--- jon matt <jonmath2000@y...> wrote:
> >
> > Here is the code..
> > <%@ Language=VBScript %>
> > <%
> > Response.Buffer=true
> > MyWhereClause = request("whereClause") // holds
> > where somefiled=somevalue and other field
> > someother value
> > Set Conn = Server.CreateObject("ADODB.Connection")
> > Conn.Open Application("MyConnection")
> > Set rs=Conn.Execute("ProcedureName " & chr(34) &
> > MyWhereClause & chr(34))// call to stored procedure
> > Do until rs.EOF %>
> > <tr>
> > <td><%=trim(rs("name"))%></td>
> > <td><%=trim(rs("age"))%></td>
> > <td><%=trim(rs("rollno"))%></td>
> > </tr>
> > <%
> > rs.MoveNext
> > Loop
> > rs.close
> > set rs= nothing
> > Conn.Close
> > set Conn = nothing
> > %>
> >
> >
> > If I use the same where clause in query analyser to
> > execute stored procedure which is very very quick.
> > Cheers
Message #6 by =?iso-8859-1?q?jon=20matt?= <jonmath2000@y...> on Wed, 5 Feb 2003 18:04:20 +0000 (GMT)
|
|
Hi Imar,
I tried that also but the result is same.
The number of records returned around 50 thousand.
I don't understand how I am gonna tune this fast.
Regards
Imar Spaanjaars <Imar@S...> wrote:In addition to this, you could use GetRows() to get your data into a two
dimensional array. That will perform a lot faster than using the MoveNext()
method.
Check out the following URL for a tutorial on this.
http://www.aspalliance.com/mbrink1111/getrows/getrows.asp
Cheers,
Imar
At 06:54 AM 2/5/2003 -0800, you wrote:
>In most cases, Query Analyzer is always going to be
>faster.
>
>One suggestion I would make would be to change your
>code where you are looping through the recordset to
>display the records.
>
>The way you are doing it now, you are context
>switching about 8 times per record. Change it to
>something like this:
>
>do until RS.EOF
>
>response.write ("" & vbcrlf & ""
>rs.fields("FieldName") & vbcrlf & "" & vbcrlf &
>"" & vbcrlf)
>
>RS.movenext
>
>Now, this may or may not make much of a difference.
>Depends on the number of records. If you were
>displaying 200+ records, you could see a difference.
>But if it's < 50, you might not.
>
>Mark.
>--- jon matt wrote:
> >
> > Here is the code..
> >
> > > > Response.Buffer=true
> > MyWhereClause = request("whereClause") // holds
> > where somefiled=somevalue and other field
> > someother value
> > Set Conn = Server.CreateObject("ADODB.Connection")
> > Conn.Open Application("MyConnection")
> > Set rs=Conn.Execute("ProcedureName " & chr(34) <br>> > MyWhereClause & chr(34))// call to stored
procedure
> > Do until rs.EOF %>
> >
> >
> >
> >
> >
> > > > rs.MoveNext
> > Loop
> > rs.close
> > set rs= nothing
> > Conn.Close
> > set Conn = nothing
> > %>
> >
> >
> > If I use the same where clause in query analyser to
> > execute stored procedure which is very very quick.
> > Cheers
---------------------------------
With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Message #7 by Imar Spaanjaars <Imar@S...> on Wed, 05 Feb 2003 19:11:14 +0100
|
|
50,0000?????? And you expect your visitor to read all that?????
IMO, you're better of using paging, where you divide all your records in
"pages" of 20 records or so. Providing the user with Next and Previous
links / buttons will enable them to view one page at the time. This
enhances usability and browsing experience.
For more info about paging, check out:
http://www.adopenstatic.com/experiments/recordsetpaging.asp
If your data needs to be printed (i.e. a report) I think you are better of
generating the stuff at the server and present the user with a link to
download it.
HtH
Imar
At 06:04 PM 2/5/2003 +0000, you wrote:
>Hi Imar,
>I tried that also but the result is same.
>The number of records returned around 50 thousand.
>I don't understand how I am gonna tune this fast.
>Regards
>
> Imar Spaanjaars wrote:In addition to this, you could use GetRows() to
> get your data into a two
>dimensional array. That will perform a lot faster than using the MoveNext()
>method.
>
>Check out the following URL for a tutorial on this.
>
>http://www.aspalliance.com/mbrink1111/getrows/getrows.asp
>
>Cheers,
>
>Imar
Message #8 by =?iso-8859-1?q?jon=20matt?= <jonmath2000@y...> on Thu, 6 Feb 2003 09:50:54 +0000 (GMT)
|
|
Hi Imar,
Thanks for all your help. Yes you are right, data needs to be printed (i.e. a report that ti in excel.). I didn't understand of
generating the stuff at the server.
How you do that ? Can you give me an examle how to that and provide a link?
Thanks again.
Jon
---------------------------------
With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Message #9 by Imar@S... on Thu, 6 Feb 2003 10:52:45
|
|
Hi Jon,
It all depends what your requirements are and whether you're willing to
spend some money.
One option is to generate a comma separated file at the server using the
FileSystemObject. If you stream that to the browser, it will either be
downloaded or opened in Excel (depending on the client browser).
For more info on the FSO go here (watch out for wrapping):
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/script56/html/FSOoriFileSystemObject.asp?frame=true
Alternatively, you could buy a third part component.For example
Softartisans offer a component that can generate Excel sheets on the fly
at the server. It supports extensive layout, calculations etc. Check it
out at http://excelwriter.softartisans.com/
If your requirements are simple, columns based files I'd go for option 1.
Cheers,
Imar
Hi Imar,
Thanks for all your help. Yes you are right, data needs to be printed
(i.e. a report that ti in excel.). I didn't understand of generating the
stuff at the server.
How you do that ? Can you give me an examle how to that and provide a link?
Thanks again.
Jon
Message #10 by =?iso-8859-1?q?jon=20matt?= <jonmath2000@y...> on Thu, 6 Feb 2003 11:39:19 +0000 (GMT)
|
|
Thanks again.
Reqirement are simple.
1) Client is gonna search based on certain selections(from multiple list boxes and some entered values)
2) Query the search against database
3) Show the report in excel.
Can you show me an example to generate a comma separated file at the server using the FileSystemObject and stream that to the
browser in Excel .
Client browser is always gonna be I.E 5 OR ABOVE.
Regards
Jon
Imar@S... wrote:Hi Jon,
It all depends what your requirements are and whether you're willing to
spend some money.
One option is to generate a comma separated file at the server using the
FileSystemObject. If you stream that to the browser, it will either be
downloaded or opened in Excel (depending on the client browser).
For more info on the FSO go here (watch out for wrapping):
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/script56/html/FSOoriFileSystemObject.asp?frame=true
Alternatively, you could buy a third part component.For example
Softartisans offer a component that can generate Excel sheets on the fly
at the server. It supports extensive layout, calculations etc. Check it
out at http://excelwriter.softartisans.com/
If your requirements are simple, columns based files I'd go for option 1.
Cheers,
Imar
Hi Imar,
Thanks for all your help. Yes you are right, data needs to be printed
(i.e. a report that ti in excel.). I didn't understand of generating the
stuff at the server.
How you do that ? Can you give me an examle how to that and provide a link?
Thanks again.
Jon
---------------------------------
With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
Message #11 by Imar Spaanjaars <Imar@S...> on Thu, 06 Feb 2003 20:18:24 +0100
|
|
Hi Jon,
Below you'll find an example of how to do this. Please be aware that this
is a limited example. I.e. I use GetString to get the entire contents of
the recordset in a string.This will work OK for hundreds, maybe a few
thousands records, but you may get into troubles with 50,000 records. You
can modify this example so it gets a limited number of rows (the second
param of GetString is used to limit the number of records).
Look in the MSDN for more info on important techniques like Server.MapPath,
the FileSystemObject, CreateTextFile etc. etc.
If you have any more questions, feel free to ask here.
Cheers,
Imar
<%
Option Explicit
Const adClipString = 2
Const ForWriting = 2
Dim objConnection
Dim rsResults
Dim sConnection
Dim sFullFileName
Dim sFileName
' Define connection string. SQL Server in this case
sConnection = "Provider=SQLOLEDB;" & _
"Data Source=SQLServerName;" & _
"Initial Catalog=DatabaseName;" & _
"trusted_connection=yes"
' Create and open connection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open sConnection
Set rsResults = objConnection.Execute("SELECT Just, The, Fields, You, Need
FROM YourTable")
If Not rsResults.EOF Then
' Results found. Get them in an string
Dim sResults
Dim objFileSystemObject
Dim objTextStream
' GetString returns one long string with the contents
' of the recordset. I use vbTab (a tab) and vbCrlf
' (a carriage return / linefeed as the column and row delimiters.
sResults = rsResults.GetString(adClipString, , vbTab, vbCrLf)
Set objFileSystemObject = Server.CreateObject(_
"Scripting.FileSystemObject")
' Create a text file and dump the contents of the string to the file
Set objTextStream = objFileSystemObject.CreateTextFile _
(Server.MapPath("export.xls"), ForWriting, True)
objTextStream.Write(sResults)
' Clean up
objTextStream.Close
Set objTextStream = Nothing
Set objFileSystemObject = Nothing
' Stream the file to the browser. Uses a helper function called
sFullFileName = Server.MapPath("export.xls")
sFileName = "Export.xls"
' Offer the file as a download
' Most browsers will present a "save as" and "open" dialog.
With Response
.ContentType = "application/vnd.ms-excel"
.AddHeader "Content-Disposition", "filename=" & chr(34) & _
sFileName & chr(34)
.BinaryWrite GetBinaryFile(sFullFileName)
End With
Else
Response.Write("No Results")
End If
rsResults.Close
objConnection.Close
Set rsResults = Nothing
Set objConnection = Nothing
Function GetBinaryFile(ByVal sFileSpec)
Const adTypeBinary = 1
Dim objStream
Set objStream = Server.Createobject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile sFileSpec
GetBinaryFile = objStream.read
Set objStream = Nothing
End Function
%>
At 11:39 AM 2/6/2003 +0000, you wrote:
>Thanks again.
>Reqirement are simple.
>1) Client is gonna search based on certain selections(from multiple list
>boxes and some entered values)
>2) Query the search against database
>3) Show the report in excel.
>Can you show me an example to generate a comma separated file at the
>server using the FileSystemObject and stream that to the browser in Excel .
>Client browser is always gonna be I.E 5 OR ABOVE.
>Regards
>Jon
>
>
> Imar@S... wrote:Hi Jon,
>
>It all depends what your requirements are and whether you're willing to
>spend some money.
>
>One option is to generate a comma separated file at the server using the
>FileSystemObject. If you stream that to the browser, it will either be
>downloaded or opened in Excel (depending on the client browser).
>
>For more info on the FSO go here (watch out for wrapping):
>
>http://msdn.microsoft.com/library/default.asp?url=/library/en-
>us/script56/html/FSOoriFileSystemObject.asp?frame=true
>
>Alternatively, you could buy a third part component.For example
>Softartisans offer a component that can generate Excel sheets on the fly
>at the server. It supports extensive layout, calculations etc. Check it
>out at http://excelwriter.softartisans.com/
>
>If your requirements are simple, columns based files I'd go for option 1.
>
>Cheers,
>
>Imar
>
>
>
>Hi Imar,
>Thanks for all your help. Yes you are right, data needs to be printed
>(i.e. a report that ti in excel.). I didn't understand of generating the
>stuff at the server.
>
>How you do that ? Can you give me an examle how to that and provide a link?
>
>Thanks again.
>
>Jon
>
>
>
>---------------------------------
>With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits
>your needs
>
>
Message #12 by =?iso-8859-1?q?jon=20matt?= <jonmath2000@y...> on Fri, 7 Feb 2003 14:27:49 +0000 (GMT)
|
|
Hi Imar,
Thanks for all your help. I'll look into example and if I need any any help I will come back to you.
Thanks
Jon
Imar Spaanjaars <Imar@S...> wrote:Hi Jon,
Below you'll find an example of how to do this. Please be aware that this
is a limited example. I.e. I use GetString to get the entire contents of
the recordset in a string.This will work OK for hundreds, maybe a few
thousands records, but you may get into troubles with 50,000 records. You
can modify this example so it gets a limited number of rows (the second
param of GetString is used to limit the number of records).
Look in the MSDN for more info on important techniques like Server.MapPath,
the FileSystemObject, CreateTextFile etc. etc.
If you have any more questions, feel free to ask here.
Cheers,
Imar
Option Explicit
Const adClipString = 2
Const ForWriting = 2
Dim objConnection
Dim rsResults
Dim sConnection
Dim sFullFileName
Dim sFileName
' Define connection string. SQL Server in this case
sConnection = "Provider=SQLOLEDB;" & _
"Data Source=SQLServerName;" & _
"Initial Catalog=DatabaseName;" & _
"trusted_connection=yes"
' Create and open connection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open sConnection
Set rsResults = objConnection.Execute("SELECT Just, The, Fields, You, Need
FROM YourTable")
If Not rsResults.EOF Then
' Results found. Get them in an string
Dim sResults
Dim objFileSystemObject
Dim objTextStream
' GetString returns one long string with the contents
' of the recordset. I use vbTab (a tab) and vbCrlf
' (a carriage return / linefeed as the column and row delimiters.
sResults = rsResults.GetString(adClipString, , vbTab, vbCrLf)
Set objFileSystemObject = Server.CreateObject(_
"Scripting.FileSystemObject")
' Create a text file and dump the contents of the string to the file
Set objTextStream = objFileSystemObject.CreateTextFile _
(Server.MapPath("export.xls"), ForWriting, True)
objTextStream.Write(sResults)
' Clean up
objTextStream.Close
Set objTextStream = Nothing
Set objFileSystemObject = Nothing
' Stream the file to the browser. Uses a helper function called
sFullFileName = Server.MapPath("export.xls")
sFileName = "Export.xls"
' Offer the file as a download
' Most browsers will present a "save as" and "open" dialog.
With Response
.ContentType = "application/vnd.ms-excel"
.AddHeader "Content-Disposition", "filename=" & chr(34) & _
sFileName & chr(34)
.BinaryWrite GetBinaryFile(sFullFileName)
End With
Else
Response.Write("No Results")
End If
rsResults.Close
objConnection.Close
Set rsResults = Nothing
Set objConnection = Nothing
Function GetBinaryFile(ByVal sFileSpec)
Const adTypeBinary = 1
Dim objStream
Set objStream = Server.Createobject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile sFileSpec
GetBinaryFile = objStream.read
Set objStream = Nothing
End Function
%>
At 11:39 AM 2/6/2003 +0000, you wrote:
>Thanks again.
>Reqirement are simple.
>1) Client is gonna search based on certain selections(from multiple list
>boxes and some entered values)
>2) Query the search against database
>3) Show the report in excel.
>Can you show me an example to generate a comma separated file at the
>server using the FileSystemObject and stream that to the browser in Excel .
>Client browser is always gonna be I.E 5 OR ABOVE.
>Regards
>Jon
>
>
> Imar@S... wrote:Hi Jon,
>
>It all depends what your requirements are and whether you're willing to
>spend some money.
>
>One option is to generate a comma separated file at the server using the
>FileSystemObject. If you stream that to the browser, it will either be
>downloaded or opened in Excel (depending on the client browser).
>
>For more info on the FSO go here (watch out for wrapping):
>
>http://msdn.microsoft.com/library/default.asp?url=/library/en-
>us/script56/html/FSOoriFileSystemObject.asp?frame=true
>
>Alternatively, you could buy a third part component.For example
>Softartisans offer a component that can generate Excel sheets on the fly
>at the server. It supports extensive layout, calculations etc. Check it
>out at http://excelwriter.softartisans.com/
>
>If your requirements are simple, columns based files I'd go for option 1.
>
>Cheers,
>
>Imar
>
>
>
>Hi Imar,
>Thanks for all your help. Yes you are right, data needs to be printed
>(i.e. a report that ti in excel.). I didn't understand of generating the
>stuff at the server.
>
>How you do that ? Can you give me an examle how to that and provide a link?
>
>Thanks again.
>
>Jon
>
>
>
>---------------------------------
>With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits
>your needs
>
>
---------------------------------
With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
|
|
|
View Cart
