| Author |
 Topic  |
|
|
Nathan
Help Moderator
USA
7664 Posts |
 Posted - 05 April 2002 : 03:16:45
|
It might help the search page if the user could choose from recieveing like between 5-100 results.
Is this posible?
Nathan Bales - Romans 15:13
----------------------------------
Snitz Exchange | Do's and Dont's |
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 05 April 2002 : 03:23:12
|
Yes, it should be possible. I've thought about it as well, just haven't ever implemented it.
if you use the following function, it's even easier: (this function will be included in v3.4)
'## Function to limit the amount of records to retrieve from the database
Function TopSQL(strSQL, lngRecords)
if ucase(left(strSQL,7)) = "SELECT " then
select case strDBType
case "sqlserver"
TopSQL = "SET ROWCOUNT " & lngRecords & vbcrlf & strSQL & vbcrlf & "SET ROWCOUNT 0"
case "access"
TopSQL = "SELECT TOP " & lngRecords & mid(strSQL,7)
case "mysql"
strSQL1 = Mid(strSQL, 1, Instr(strSQL, ";")-1)
strSQL2 = Mid(strSQL, InstrRev(strSQL, ";"))
TopSQL = strSQL1 & " LIMIT " & lngRecords & strSQL2
end select
else
TopSQL = strSQL
end if
End Function
an example of it's use:
rs.open TopSQL(strSql,1), my_Conn |
 |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 April 2002 : 07:32:24
|
Should help to some degree anyway.
Unfortunately the problems not always the number of results returned, but the number of records having to be searched, 5 results could still time out if those 5 posts happen to be 8000 back in the database.
www.daoc-halo.com |
|
|
|
RCHAWAII
Junior Member
USA
277 Posts |
Posted - 05 April 2002 : 09:48:33
|
quote:
Should help to some degree anyway.
Unfortunately the problems not always the number of results returned, but the number of records having to be searched, 5 results could still time out if those 5 posts happen to be 8000 back in the database.
www.daoc-halo.com
Which is the best solution?
http://www.ls3k.com/snitz/topic.asp?TOPIC_ID=104
http://www.rcamericaonline.com -The Best Radio Control Flying Forum on the Web. |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 April 2002 : 19:24:04
|
Personally I think the best solution is to search the records in say groups of 1000 but I'm pretty sure not all of the DB types Snitz supports has the ability to do this, which would make it something not easily implemented for everyone.
I haven't had a close look at search.asp really yet, but I do suspect you'll see some of the optimisation work that the Dev's have been doing to 3.4 have a nice side-effect on seatch.asp response times too, I remember the playing around with active.asp I did a few weeks ago reduced the calls search.asp was making for me by a couple of thousand.
www.daoc-halo.com |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 11 July 2002 : 15:51:32
|
I've added an option for oracle (shown in red). Might be useful just incase Snitz ever supports oracle.
'## Function to limit the amount of records to retrieve from the database
Function TopSQL(strSQL, lngRecords)
if ucase(left(strSQL,7)) = "SELECT " then
select case strDBType
case "sqlserver"
TopSQL = "SET ROWCOUNT " & lngRecords & vbcrlf & strSQL & vbcrlf & "SET ROWCOUNT 0"
case "access"
TopSQL = "SELECT TOP " & lngRecords & mid(strSQL,7)
case "mysql"
strSQL1 = Mid(strSQL, 1, Instr(strSQL, ";")-1)
strSQL2 = Mid(strSQL, InstrRev(strSQL, ";"))
TopSQL = strSQL1 & " LIMIT " & lngRecords & strSQL2
case "oracle"
TopSQL = "SELECT * FROM (" & strSQL & ") WHERE ROWNUM <= " & lngRecords
end select
else
TopSQL = strSQL
end if
End Function
Missed out an equals sign, edited it change < to a <=
Edited by - pweighill on 12 July 2002 06:46:40 |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 11 July 2002 : 16:25:52
|
quote:
I've added an option for oracle (shown in red). Might be useful just incase Snitz ever supports oracle.
'## Function to limit the amount of records to retrieve from the database
Function TopSQL(strSQL, lngRecords)
if ucase(left(strSQL,7)) = "SELECT " then
select case strDBType
case "sqlserver"
TopSQL = "SET ROWCOUNT " & lngRecords & vbcrlf & strSQL & vbcrlf & "SET ROWCOUNT 0"
case "access"
TopSQL = "SELECT TOP " & lngRecords & mid(strSQL,7)
case "mysql"
strSQL1 = Mid(strSQL, 1, Instr(strSQL, ";")-1)
strSQL2 = Mid(strSQL, InstrRev(strSQL, ";"))
TopSQL = strSQL1 & " LIMIT " & lngRecords & strSQL2
case "oracle"
TopSQL = "SELECT * FROM (" & strSQL & ") WHERE ROWNUM < " & lngRecords
end select
else
TopSQL = strSQL
end if
End Function
You really shouldn't use select * in a web based query.
|
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 11 July 2002 : 16:48:13
|
quote:
You really shouldn't use select * in a web based query.
Normally you wouldn't but in this case, the SELECT text that you sould pass to the function would handle that. And the second select * is just to get all those results again, but restricting it to the first n rows.
You can't just add the WHERE ROWNUM < n to the end of the SELECT statement beacuse it won't work properly if there is an ORDER BY clause, so it needs to be SELECTED a second time.
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 11 July 2002 : 17:03:13
|
I don't see how that helps, the reson you shouldn't use * is because of field orderring in the result set, unless you explicitly define them youu can't guarentee that all memo fields appear at the end, something required by ADO to retrieve tha data correctly
|
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 11 July 2002 : 18:05:41
|
quote:
I don't see how that helps, the reson you shouldn't use * is because of field orderring in the result set, unless you explicitly define them youu can't guarentee that all memo fields appear at the end, something required by ADO to retrieve tha data correctly
I thought there some other unimportant reason why not to use select * I will have to go and rewrite some queries now. I was going to do it anyway because Getrows and the arrays was giving me a hard time, not nowing which column in the array it was. Just * is so much easier than writing out table.column twenty+ times  Oh well.
|
|
|
| |
Topic |
|
Topic Locked
