Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Straight to filter page

  1. #1
    Join Date
    Nov 2003
    Posts
    11

    Straight to filter page

    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?

  2. #2
    Join Date
    Oct 2002
    Posts
    933
    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

  3. #3
    Join Date
    Nov 2003
    Posts
    11
    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.

  4. #4
    Join Date
    Oct 2002
    Posts
    933
    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

  5. #5
    Join Date
    Nov 2003
    Posts
    11
    By startup filter, you mean dbFilterOnEntry?

  6. #6
    Join Date
    Oct 2002
    Posts
    933
    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.

  7. #7
    Join Date
    Nov 2003
    Posts
    11
    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?

  8. #8
    Join Date
    Oct 2002
    Posts
    933
    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

  9. #9
    Join Date
    Nov 2003
    Posts
    11
    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.

  10. #10
    Join Date
    Oct 2002
    Posts
    933
    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.

  11. #11
    Join Date
    Nov 2003
    Posts
    11
    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.
    Last edited by jboyd; 11-07-2003 at 01:02 PM.

  12. #12
    Join Date
    Oct 2002
    Posts
    933
    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.

  13. #13
    Join Date
    Oct 2002
    Posts
    933
    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

  14. #14
    Join Date
    Nov 2003
    Posts
    11
    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.

  15. #15
    Join Date
    Oct 2002
    Posts
    933
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •