Sql Oddity
Mar 9, 2004
alright here is an sql statement i am using
SELECT t_Provider.ProviderID
FROM t_Location INNER JOIN
t_Provider ON t_Location.LocationID = t_Provider.LocationID INNER JOIN
t_Source ON t_Provider.ProviderID = t_Source.ProviderID
WHERE (t_Location.StateID = 25)
and currently i have 2 rows in my location table with the stateID of 25 and 2 of the providers have the same location id so it should bring up 3
should bring up this
|
V
providerid location id
112 147
151 147 -- I had the same results without this value entered
114 149
but it brings up three and did before i added the center record too
and their values are
112
114
112
why is it bringing up 2 of the same and why does it ignore when i add another record
View 2 Replies
May 22, 2007
I have two servers running the same report server project with the same webapplication front end.
Both instances of SQL are on SP2.
When I drilldown in the report viewer on server A, and click next page it returns to the parent report. (which obviously it should go to the next page of the current report) Sometimes this odd behavior happens and sometimes it doesn't.
However on server B, everything works fine.
Note: the installation on server A went a littly crazy. Am I missing a service pack that would have fixed this?
View 1 Replies
View Related
May 4, 2007
Hello all,
I'm going to implement an inverted index data structure that relies, for performance consideration, on partinioned views.
I've created a set of tables with a column named "PartitionKey" that deserve to partition the view. Then I've created a view using the UNION ALL as prescribed. The partinion works.
Now I'm devising the right SELECT query to benefit from the partition but I'm getting strange query plan.
"word" is the partinioned view and "vocabulary" is a referenced table.
If I write something like this:
select * from
vocabulary v
inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey
where
word = 'foobar'
and w.partitionkey = 1 -- It works correctly even with the condition v.partitionkey = 1
The query plan, correctly, shows that the only table actual accessed is "word1", the one related to the partinionkey value 1. ==> OK
If I write something like this, that appears more intuitively:
select * from
vocabulary v
inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey
where
word = 'foobar'
I get a concatenation operation that involved all the "wordXX" tables. I deduce that the engine is not so "clever" to benefit from the join hint.
So, I rewrite the previous as the following, thinking to feed a better infotmation to the engine:
select * from
vocabulary v
inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey
where
word = 'foobar'
and w.partitionkey = (select partitionkey from vocabulary where word = 'foobar')
That resembles the first example but... it doesn't work: I still get a concatenation operation.
I'm very frustrated and than rewrite again the query in a T-SQL script such this:
declare @partitionkey as int
set @partitionkey = (select partitionkey from vocabulary where word = 'foobar')
select * from
vocabulary v
inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey
where
word = 'foobar'
and w.partitionkey = @partitionkey
And I still get a concatenation operation.
I can understand the behaviour of the second and third example (the query optimizer doesn't have an actual value for the partition key) but the fourth is unbeliavable. Even if I'm feeding a query with a fixed value it cannot benefit of it at all.
Where am I wrong? Is so "silly" the qwery optimizer? How can I force the expected behaviour?
I would say more that the "partitionkey" is generated randomly when a new word is initially inserted into the parent table "vocabulary" so I cannot compute it before to access the "word" table.
I've tested on SQL2005. If anyone is interested I can provide the queryplans generated for all the above examples.
View 7 Replies
View Related
