Board index SyBase

simulating query-by-example without horrible performance

simulating query-by-example without horrible performance

Post by Mark Seid » Wed, 14 Feb 1996 04:00:00



under 4.9.2, we are trying without much luck to fill in a form to have
the effect of something like QBE -- the user fills in as many fields
as they know, and a stored procedure is called which does an AND query
on all of those that have been supplied.

unfortunately, the tables involved are quite large, so we're trying to
avoid table scans..  all of the fields we're searching
on are indexed.

so we tried:




etc. for all the fields in the form
AND equijoins on key fields in table1, table2, table3, table4

takes FOREVER...


is forcing a table scan on table2... and the execution plan is entirely
different.

when we comment it out, everything works nicely.  

unfortunately, upgrading to a later data server is not possible, and.
unrolling the query would result in 512 different queries.

we are slighly stumped.

any hints?

 
 
 
Top

1. Horrible Performance In MS Analysis Services - Great Performance In SQL 2000

I appreciate anyone's help on this....

I have been working with data on our SQL Server (2000) latest SP's.
Machine is a P4 1.8 Ghz / 120 GB HD / 512 MB Ram

When on SQL server I have constructed fact tables by week (i.e. a
week's worth of sales data is in each table - Sales200201, Sales
200202, etc) - each week is about 200,000 rows [There is a field for
Week (int field), Store Number(int field), and UPC (char 13 field)
that tie to the "dimension" tables respectively]

I have ProductDescription (UPC) table that has ALL the characteristics
of every product (around 7,000 rows) and a Store Location (Store
Number) Table (3,000 rows) and a Time Table (104 rows - 2 years worth
of Week dates)

Now in T-SQL with an ugly query - 5 different parts to a where clause
that include groups of stores, groups of products, and descriptions of
products the query and even a union of say 4 weeks returns in about 11
seconds.

Over in MS Analysis Services Enterprise SP2, when I query the same
cube using the Browse interface in MS Analysis Svc or Proclarity or MS
Excel, I can sit for minutes to an hour waiting for results to come
back.

In MS Analysis Services, I have added each week to the sales cube as
its own partition, told MS Analysis Services which weeks are in which
partition(SLICE), set aggregations very low initially (to be improved
by User Optimization Wizard), optimized the cube, minimized the number
of dimensions (though on the Store Dimension there is a lot of
ancillary store data I put in the Properties of the Store Nbr member)
and made all partitions use the original partition's aggregations for
possible merging later.

Does anyone know why my performance in SQL Server is so great and as
expected and in MS Analysis Services it is so horrible?  Any ideas on
how to resolve this?

2. JSP and database access

3. Horrible Performance with SP

4. MS Access to DB2

5. The performance is horrible

6. SQL server JDBC driver don't support resultset.last()?

7. HORRIBLE ORACLE PERFORMANCE PROBLEM

8. sybase 11.0.3 and AIX raw devices - horrible performance

9. ASE-DB-Library HORRIBLE-Performance !!!

10. Parallel query performance worse than without it

11. Delphi BDE and QBE example (IE: Using Query By Example instead of SQL)

12. Simulating and Modelling db performance


All times are UTC
Board index