November 9, 2006 at 2:52 am
I recently found some sample code in vbscript that basically allowed you to download an Access database "in chunks" to an ASP intranet webpage, this enabled the pages to be written very quickly. With slight modifications I've got it to work with a database from SQL Server. I can't get it to work via a stored procedure. The stored procedure I wanted to use is very basic, i.e SELECT * from [data]
can anyone help please
November 9, 2006 at 9:50 pm
Here's an example I made for the boys at work... the Customer Table has about 1.2 million rows in it... page "0" (page 1) will return in about 16 milliseconds. The last page (page 12000) will return in about 450 milliseconds (PK on CustID at the very least). This would be very easy to turn into a stored procedure and both the Page Number and Page Size are "programmable"...
--===== Declare the local variables
DECLARE @PageSize INT --How many rows to appear per page
DECLARE @PageNum INT --What page number to appear
DECLARE @Skip INT --Working variable for where to start for page
DECLARE @sql VARCHAR(8000) --Holds dynamic SQL
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 100
SET @PageNum = 4000
SET @Skip = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
SET @sql =
'
SELECT c.*
FROM dbo.Customer c,
(
SELECT TOP '+STR(@PageSize)+ ' *
FROM dbo.Customer WITH (NOLOCK)
WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID
FROM dbo.Customer
ORDER BY CustID)
ORDER BY CustID
) d
WHERE c.CustID = d.CustID
'
--===== Produce the page
EXEC (@SQL)
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2006 at 2:15 am
Thanks Jeff, at first glance as you've said it should be easy to change this into a SP, can I get back to you when I try this out later in the week?
November 13, 2006 at 5:18 pm
You bet, Mike... and thanks for the feedback...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2006 at 7:46 am
Jeff, many thanks again. I've adapted your code to work with my database, The only thing is unless I use it on a field that is unique the results are not accurate. I have a table called Data with a number of fields called [Name], [number], [address1] etc. I had to make the field [Number] unique to get the correct data in the right order. Is there a way of making this work on a database that doesn't have unique fields? your modified code is below. By the way, you comments on the code are excellent, I've sort of worked out how it works
DECLARE @PageSize INT --How many rows to appear per page
DECLARE @PageNum INT --What page number to appear
DECLARE @Skip INT --Working variable for where to start for page
DECLARE @sql VARCHAR(8000) --Holds dynamic SQL
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 5
SET @PageNum = 1
SET @Skip = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
SET @sql = 'SELECT c.* FROM dbo.data c,(SELECT TOP '+STR(@PageSize)+ ' * FROM dbo.data WITH (NOLOCK)
WHERE [number] NOT IN (SELECT TOP '+STR(@Skip)+' [number] FROM dbo.data ORDER BY [number])ORDER BY [name]) d
WHERE c.[number] = d.[number] '
--===== Produce the page
EXEC (@SQL)
November 14, 2006 at 8:11 am
No, sorry... in order to guarantee that the pages don't overlap or repeat, you need something to absolutely guarantee the uniqueness of each row... and it doesn't necessarily need to be a number. In my example code, CustID is the Primary Key. Use the Primary Key of your table as the substitute for CustID (I think you already did that with the NUMBER column).
By the way... both Order By's need to be on the same columns in order to preserve the sort of the return... you have ORDER BY [number] in the inner query and ORDER BY [name] in the outer query. Change them both to [name]...
SET @sql =
'
SELECT c.*
FROM dbo.Customer c,
(
SELECT TOP '+STR(@PageSize)+ ' *
FROM dbo.Data WITH (NOLOCK)
WHERE Number NOT IN (SELECT TOP '+STR(@Skip)+' Number
FROM dbo.Customer
ORDER BY Name)
ORDER BY Name
) d
WHERE c.CustID = d.CustID
'
You need to have a good index on the Order By column(s) or this will be a dog for sure... I haven't played with sorting on the non Primary Key columns on this puppy, though. I give something like your sort a try tonight after I get home.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2006 at 8:31 am
Thanks Jeff, I appreciate the time you've spent on this
November 14, 2006 at 9:18 am
Jeff, I know I'm being really cheeky here, can you help with out with another problem, totally unrelated from this posting? although it's still to do with my intranet site
I'd like to be able to save and read images to my sql database using Stored procedures. I've seen various bits of code that use what I call "direct access" to the server, but I think using SP's would be far easier especially with VBscript on a webpage. I've tried using Google and I've posted a request here a while ago but no luck.
November 14, 2006 at 6:21 pm
I think the reason you've had no luck with the other post is because most of the folks on this forum will likely tell you that storing images in the database is one of those "Death by SQL" things... everyone I've had to work with stores only the filename in the database. The app get's the file name and uses that to retrieve the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2006 at 1:53 am
Ok Jeff, thanks anyway. Thanks again for the assistance
November 17, 2006 at 6:54 am
With Jeff's help I've come up with the following SP and VB script for a web based database that allows you to retrieve a small amount of records per page - first the Stored Procedure
CREATE PROCEDURE spCDI_Data_Paged @PageNum as int
AS
DECLARE @PageSize INT --How many rows to appear per page
--DECLARE @PageNum INT --What page number to appear
DECLARE @Skip INT --Working variable for where to start for page
DECLARE @sql VARCHAR(8000) --Holds dynamic SQL
SELECT count(*) from [data]
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 15
-- SET @PageNum = 1
SET @Skip = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
SET @sql = 'SELECT c.* FROM dbo.data c,(SELECT TOP '+STR(@PageSize)+ ' * FROM dbo.data WITH (NOLOCK)
WHERE [number] NOT IN (SELECT TOP '+STR(@Skip)+' [number] FROM dbo.data ORDER BY [name])ORDER BY [name]) d
WHERE c.[number] = d.[number] '
--===== Produce the page
EXEC (@SQL)
GO
November 17, 2006 at 6:56 am
Then the HTML/vbscript
<html>
<!-- #include file ="../include/connection.asp" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>
<%
dim strSPName, oConn, intRtnType ,rs, strValue , strOption,surl,dateofCall,aryParams(1),iRecCount,iPageNum , iPageSize , iTmpCounter
If Request.QueryString("page") = "" Then
sPageNum = 0
Else
sPageNum = CInt(Request.QueryString("page"))
End If
DisplayPages()
function DisplayPages()
'iPageNum = 1
intRtnType = 1
strSPName = "spCDI_Data_Paged"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connCDI
aryParams(1) = sPageNum ' page number
Set Rs = RunSP(strSPName, oConn , aryParams, intRtnType)
iRecCount = rs.fields(0).value
'iRecCount is returned from stored proc
'sPageNum is passed into the page (set to zero for first time round)
iPageNum = Round(iRecCount/20)
If iPageNum < (iRecCount/20) Then iPageNum = iPageNum +1
Response.Write "Total amount of phone numbers in report = " & iRecCount & " <b>Page: "
For iTmpCounter = 0 to iPageNum -1
if iTmpCounter = cint(sPageNum) then
Response.Write " " & iTmpCounter
else
Response.Write " <a href=paged_data.asp?Page=" & iTmpCounter & ">" & iTmpCounter & "</a>"
end if
Next
response.write "<BR>"
response.write "<BR>"
response.write "<BR>"
myhtml=myhtml & "<table border='1' width='100%'><tr><td width='40%'>Name</td><td width='15%'>Phone Number</td><td width='20%'>Address1</td><td width='20%'>Time of Call</td> </tr>"
Set rs = Rs.NextRecordset()
With Rs
Do Until .EOF
myhtml=myhtml & "<tr>"
strName = rs.fields(0).value
strNumber = rs.fields(1).value
strAddress1 = rs.fields(2).value
myhtml=myhtml & "<td>" & strName & "</td>"
myhtml=myhtml & "<td>" & rs.fields(1).value & "</td>"
myhtml=myhtml & "<td>" & strAddress2 & "</td>"
.MoveNext
Loop
End With
oConn.Close
myhtml=myhtml & "</table>"
response.write myhtml
end function
%>
</body>
</html>
November 17, 2006 at 6:57 am
Many thanks again to Jeff for his patience
November 17, 2006 at 8:12 am
Cool... thanks for the feedback, Mick.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply