# Miscellaneous > Database Programming >  Straight to filter page

## jboyd

ASP-DB Pro. MS SQL Server 2000. OK... I need to have a page go straight to a filter page. I have dbStartup set to filter. When I try to go to the page it hangs for a while, and then times out. Running SQL Server Profiler, I see that ASP-DB has issued the following SQL: Select count(*) from MyTable. If I am going straight to a filter why is it doing a count of all the records? The table contains more than 30 million records, but once the users filter on what they are looking for, the result set will be less than 100 records. My timeout is set to five minutes. If I set it to qo minutes, it might finish before it times out, but it doesn't make sense to scan the whole 30 million records, when the actual data we will filter on will only be ~100 rows. Plus, users will not be happy about having to wait 10 minutes just to bring up a filter screen. Am I doing something wrong?

----------


## Frank

The reason for the SQL in startup filter is to get the schema and valid the input filter fields and their type. Yes, a big step just to do that. Try using X.dbExpress=true. That should stop the counting. If you need the counting which in your case seems not, then detect the first entry and toggle the dbExpress. Let me know how it goes. May be I should put in a switch for "no check", but the express should do it.


Frank

----------


## jboyd

Thanks Frank. I found the dbExpress flag but I still seem to be having some performance issues. I'll do a little more research and let you know what I find.

----------


## Frank

You can compare the following to check the performance -

Select * from Table (use startup filter to input field=value)

Select * From Table where field=value

Frank

----------


## jboyd

By startup filter, you mean dbFilterOnEntry?

----------


## Frank

No, try starup filter and manually put in the same filter and then try use the SQL with the same filter in the SQL statement and you can see whether the dbStartup=filter has performance problem.

----------


## jboyd

It has been over a year since I worked with ASP-DB, so I am a little but rusty. Can you tell me how I setup the startup filter?

----------


## Frank

OH, you original message says you use dbStartUp=Filter and you got a recording counting problem. I recommended to use dbExpress to fix that. So, use the same startup=filter and put in like Field=value (a criteria) and then re-do the ASP file using an SQL with the same criteria SELECT .... where field=value. That would expose any performance problem of the starup=filter.

If you still get stuck, call in an get it resolved. If support cannot resolve that, you can call me directly.


Frank

----------


## jboyd

Here is a piece of the code I am using:


  MyDb.dbSQL = "Select * FROM vw_GL_tran_details"
  MyDb.dbExpress = True
  MyDb.dbFilterOnEntry = " PROPERTY = '01' AND COMPANY = '00' AND CONTRACT = '000000'"
  MyDb.dbStartUp="Filter"

vw_GL_rans_details is a partitioned view that joins about 20 tables containing about 30 million records. With partitioned views, SQL Server's query processor is smart enough to limit the query to hit just the tables it knows the data is in, based on the check constraints. For example my data is partitioned on PROPERTY, so if my select is limited to a specific property, SQL Server will only scan the table that contains data for that property. The problem with the code above is that I still see ASP-DB issuing the statement "Select * from vw_GL_trans_details, which will scan all 20 tables instead of just the one that has data for the property I specified. It takes ~ 4 minutes to do this, even though I am just going straight to the filter page. It looks dbFilterOnEntry is ignored when dbStartup equals "filter". I understand that some kind or query needs to be issued in order to get the metadata to build the filter list. What would be ideal is if you could specify the format of the result set so you could go straight to a filter page without ASP-DB executing any SQL transactions.
 It will get even worse if I start using distributed partitioned views, where I have data for each property located in identical remote databases. Property 1 data may be located in a database in Florida, and property 2 data may be located in a database in Hawaii. If I am in Florida, and querying on data just for the florida property, when ASP-DB does the initial query without a where clause it will still scan the entire table in Hawaii.

----------


## Frank

Your code is equivalent to -

Select * FROM vw_GL_tran_details Where PROPERTY = '01' AND COMPANY = '00' AND CONTRACT = '000000'

The only thing FilterOnEntry is to let you  build the SQL with the criteria.

vw_GL_tran_details is a view and it should be like a table to ASP-db.

Another way to check it is not to use any ASPdb. USe the PureASP example and change the params to your "View" and check the response time. I have a feeling it is in the SQL setup.

----------


## jboyd

I ran profiler on the SQL being issued by ASP-DB once with dbStartup = 'Filter', and once without it.

The one without dbStartup = 'filter' did add the where clause to the select. The one with dbStartup = 'filter' issued a SQL statement without the where clause. dbFilterOnEntry is being ignored when dbStartup = 'filter' is set.

----------


## Frank

dbstarup=filter offers a way to allow setting the filter and user can change it.

filterOnEntry is controlled in the code (asp file) to lock in a filter and user cannot change it. The filter becomes the premenant part of the SQL.

These 2 properties serves different conditions and cannot be mixed.

----------


## Frank

are you saying that you need to set a filterOnEntry and then a dbStartUp=Filter and you encounter countingproblem even with dbExpress=True.

I tested the following code and it seems to work fine.

<% 
  Set X = Server.CreateObject("ASP.db")
  X.dbUnit = 10112
  X.dbDSN = "NWIND" 
  X.dbNavigationItem = "top, bottom, next, prev, Filter"
  X.dbSQL = "Select * from orders"
  X.dbFilterFlds="0,1,2,3"
  X.dbFilterOnEntry="OrderID>10300"
  X.dbStartUp="Filter"
  X.dbExpress=true
  X.dbMode = "grid"
  X.dbDebug = 101
  X.ASPdb
%>

Correction, FilteOnEntry can be changed by filter reset, otherwise, it'll be the same as hard coding the criteria in the dbSQL.

Frank

----------


## jboyd

I simply want a way for a user to go straight to a filter page quickly. Even with dbStartup = 'filter' and dbExpress set to true, I still see the following SQL being issued: Select * FROM vw_GL_tran_details. The users will only enter very restrictive filters, returning 100 rows or less. So, it doesn't make sense for ASP-DB to do the Select * FROM vw_GL_tran_details,  which would return ~30 million records.

----------


## Frank

it should not with dbExpress. set the debug level to display all the steps and send in the html dump. I would like to see where the select * comes from with dbexpress set to true.

1. Modify the above sample NWIND file to reflect your procedures

2. Check the version of your DLL and make sure youhave the latest.

3. If neccessary, make arrange a debugging session with support. send in a light version of your view file and we can set it up in the debugging server.

FK

----------


## jboyd

I have not used the Debug option before. What value do I need to set it to in order to show all steps. Also, where does it put the debug information? In a file, or on the webpage that is generated.

----------


## Frank

checkout the dbDebug of the online manual. the debug is an on-screen dump. Dump all so we can see where the SQL changed to SELECT * ... from SELECT TOP n .... after dbExpress=true

----------


## jboyd

OK... I am going to need to add a where clause to my select though, because the page times out before it can deal with the 30 million records.

----------


## Frank

if you can build the case using nwind then the debug will be much easier. All we need to see is the SELECT * .. statement.

FK

----------


## jboyd

Hi Frank. I see now that ASP-DB is issuing SET ROWCOUNT 50, because that is what size my grid is set to. I also see now, after using showplan, that MS SQL server is having problems selecting an optimal query plan when using SET ROWCOUNT. Without SET ROWCOUNT it uses a HASH join. With ROWCOUNT set it uses a LOOP join, and takes more than 5 times longer to run. Using a join hint in my query gets performance back to what I was expecting. Thank you for your help in looking into this issue Frank.

----------


## Frank

This SET ROWCOUNT stuff is controllable. If you have problem again, let me know and we'll find a way to get around that.


FK

----------


## bobc

I seem to remember having this problem some time ago. Try setting to the page maximum size to 20 - mydb.dbGridinc="20". I can't remember the syntax for the .dbGridIncMax (I'm on Tornado) which may need changing as well. This seems to stop the whole recordset being queried.

----------


## Frank

So, do we have a problem or not? If so, please be more specific the problem area and also some sample code will help.


FK

----------

