Changing items select statement causes different results for other items 
Author Message
 Changing items select statement causes different results for other items

Hi,

I'm facing a tricky problem with SQL Server and OLAP, and I've traced
the problem to a query parsing bug in SQL Server.

Analysis Server generates the following query below to query my fact
table, vw_employee_skill_set_compare.

Run as shown below, I get:
- 296 rows returned
- regional_expected_score field set to '5' in almost all cases
- query analyzer returns almost straight away

Removing the level_id item from the SELECT list:
- returns 42662 rows
- the query takes much longer
- I now get the real data.

My investigations have revealed that this depends on how many joins I
put in vw_employee_skill_set_compare -- a fairly complicated view with
subjoins within it. However, I'm now stuck since I can't control
Analysis Server's queries, and I really need to include all the joins
inside my view.

I don't have any control over the query Analysis Server generates for
itself, however, is there anything I can do to force _their_ query to
run correctly by inserting some options in my view? (I don't know,
maybe forcing a certain type of optimisation?)

The query in question is below.

I'm running SQL Server 2000 Desktop Engine, with service packs on a
Win2K Professional box. I haven't had a chance to test this on our SQL
Server Standard edition server yet.

All help appreciated,

Matthew Herrmann
Far Edge Technology

Query generated by analysis server:

SELECT  "dbo"."period"."period_id",
        "dbo"."competency_group"."competency_group_id",
        "dbo"."competency"."competency_id",
        "dbo"."vw_skill_digest"."skill_id",
        "dbo"."grade"."grade_id",
        "dbo"."stream"."stream_id",
        "dbo"."vw_employee_digest"."employee_id",
        "dbo"."rating"."rating_id",
        "dbo"."level"."level_id",
        "dbo"."vw_role_digest"."role_id",
        "dbo"."vw_employee_skill_set_compare"."regional_expected_score"

FROM "dbo"."vw_employee_skill_set_compare",
        "dbo"."period", "dbo"."competency_group",
        "dbo"."competency", "dbo"."vw_skill_digest",
        "dbo"."vw_employee_digest", "dbo"."grade",
        "dbo"."normal_role", "dbo"."stream",
        "dbo"."vw_role_digest", "dbo"."rating",
        "dbo"."level", "dbo"."location"

WHERE
        ("dbo"."vw_employee_skill_set_compare"."period_id"="dbo"."period"."period_id")
AND
        ("dbo"."competency_group"."competency_group_id"="dbo"."competency"."competency_group_id")
AND
        ("dbo"."competency"."competency_id"="dbo"."vw_skill_digest"."competency_id")
AND
        ("dbo"."vw_skill_digest"."skill_id"="dbo"."vw_employee_skill_set_compare"."skill_id")
AND
        ("dbo"."vw_employee_digest"."grade_id"="dbo"."grade"."grade_id") AND
        ("dbo"."vw_employee_skill_set_compare"."employee_id"="dbo"."vw_employee_digest"."employee_id")
AND
        ("dbo"."normal_role"."stream_id"="dbo"."stream"."stream_id") AND
        ("dbo"."normal_role"."normal_role_id"="dbo"."vw_role_digest"."normal_role_id")
AND
        ("dbo"."vw_employee_digest"."role_id"="dbo"."vw_role_digest"."role_id")
AND
        ("dbo"."vw_employee_digest"."rating_id"="dbo"."rating"."rating_id")
AND
        ("dbo"."level"."level_id"="dbo"."vw_skill_digest"."level_id") AND
        ("dbo"."vw_employee_digest"."location_id"="dbo"."location"."location_id")



Thu, 18 Nov 2004 16:10:59 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. select statement using :variable as selected item

2. Need to use a select statement to get 26 items from one table to 26 Paramaters

3. SQL statement - selecting multiple items based on complex condition

4. SELECT statement and view with same select produce different results

5. SELECT statement and view with same select produce different results

6. SQLMenu50, enable item, disable item

7. Add additional items to Unselected items in SQL Profiler for SQL Server 7.0

8. Add item in line item portal from another portal

9. How to represent items and componants of items in a database

10. dict item that will give item id length ?

11. Selecting items in a DB-list and passing fields from selected rec to a control

12. Select nth item in a select list...


 
Powered by phpBB® Forum Software