December 18, 2009 at 2:23 am
Hi,
I wrote the following query which returns a result of 13388 in 2 or 3 seconds.
SELECT COUNT(*)
FROM
(
SELECT r.cd, p.type FROM
(
SELECT DISTINCT code AS cd
FROM Prices
) r
LEFT JOIN Products p ON p.code = r.cd
) x
LEFT JOIN zExtract z ON z.code = x.cd
WHERE z.code IS NULL
x.type will either be NULL or is a value between 1 and 4.
When I append one of the following conditions to the end of the above query, I get these results:
AND x.type IS NULL -- returns 13387 (in 2 seconds)
AND x.type = 1 -- returns 1 (in 2 seconds)
AND x.type = 2 -- returns 0 (in 1 second)
AND x.type = 3 -- returns 0 (in 2 seconds)
AND x.type BETWEEN 1 AND 4 -- returns 1 (in 3 seconds)
However, the problem is when I add the condition
AND x.type = 4
I know that the result for this is 0 but the query doesn't end execution. I left it running for over an hour and it was still executing.
I have no idea why this happens. Any value of x.type seems to be work well except for 4. Even [font="Courier New"]BETWEEN 1 AND 4[/font] runs fine. I can't figure out what's going on.
Does anyone have any idea why this might be happening please?
Thanks.
December 18, 2009 at 3:16 am
Hi Wandrag,
Thanks for your query. However, it returns different results.
I'm copying my query again - I actually have a condition on the innermost select and I've renamed all code columns to stockcode. The rest remains the same.
SELECT COUNT(*)
FROM
(
SELECT r.stockcode, p.type FROM
(
SELECT DISTINCT stockcode
FROM Prices
WHERE SiteId = 10
) r
LEFT JOIN Products p ON p.stockcode = r.stockcode
) x
LEFT JOIN zExtract z ON z.stockcode = x.stockcode
WHERE z.stockcode IS NULL
AND x.type = 4
Table zExtract is actually a temporary table. Its contents were imported from a delimited text file. It has no indexes defined.
Table Prices has a non-clustered index on SiteId and a clustered index on SiteId, StockCode.
Table Products has various indexes on it but the only relevant one is the clustered primary key on StockCode.
Thanks.
December 18, 2009 at 3:46 am
Can you save the plan as a .sqlplan file? While I can read the xml, it's tedious.
Are there more rows that have type=4 than for the others?
Does an UPDATE STATISTICS on that table help?
Does adding OPTION (RECOMPILE) at the end of the query help?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 4:25 am
Hi
Thanks for your replies. I'm sorry I could not try out your suggestions and reply last week as I was very busy.
Wandrag, I updated the statistics for the temp table zExtract as follows but it made no difference
UPDATE STATISTICS zExtract WITH FULLSCAN
GilaMonster, type is either NULL or a value between 1 and 4. Almost all entries have type = null. In fact, there are 0 entries with type = 4. The results are given in my first post.
I cannot add a recompile option since this query is not in a stored procedure but is part of a script I am running to get some statistics about data in the DB.
I have now added an index on stockcode.
CREATE INDEX IX_STOCKCODE
ON zExtract (STOCKCODE)
The creation of this index solves the problem. However, I would like to understand why the query works without an index for [font="Courier New"]type = null, 1, 2, 3[/font] but not for [font="Courier New"]type = 4[/font]. It then works perfectly for any of value of type with the index.
I am attaching two execution plans for the query. One is for the query prior to the creation of the index and the other is for the query after the index was created.
Thanks for your help.
December 21, 2009 at 6:28 am
Wandrag (12/21/2009)
Hi,What about updating the stats for the rest of that tables that's being used?
Yes Could you please update the stats for Products table as well? Also please mention whether the type field in Products table is NULLABLE.
Secondly, since you mentioned there are lot of indexes on Products table and StockCode has a clustered index on it, I also have a feeling that the table may be fragmented. Could we also check out for fragmentation please?
Another point is for this particular condition x.type = 1/2/3/4, the left join on Products table is irrelevant. Only for the condition x.type IS NULL the left join holds good.
Both these queries should give the same results as the earlier one:
SELECT COUNT(*)
FROM
(
SELECT r.stockcode, p.type FROM
(
SELECT DISTINCT stockcode
FROM Prices
WHERE SiteId = 10
) r
JOIN Products p ON p.stockcode = r.stockcode
) x
LEFT JOIN zExtract z ON z.stockcode = x.stockcode
WHERE z.stockcode IS NULL
AND x.type = 4
SELECT COUNT(*)
(
SELECT DISTINCT stockcode
FROM Prices
WHERE SiteId = 10
) r
JOIN Products p ON p.stockcode = r.stockcode
LEFT JOIN zExtract z ON z.stockcode = r.stockcode
WHERE z.stockcode IS NULL
AND p.type = 4
Finally, do we by any way have a foreign key constraint on StockCode column of Prices table pointing to corresponding column of Products table?
Thanks
Satish More
December 21, 2009 at 6:43 am
What would really help; table definitions, sample data, expected results based on sample data. The sample data should reflect the problem at hand, but shouldn't be more than 10 rows of data for each table.
Need help with this, please read an follow the instructions provided in the first article I reference below in my signature block.
December 21, 2009 at 7:25 am
SELECT COUNT(*)
FROM
(
SELECT r.stockcode, p.type FROM
(
SELECT DISTINCT stockcode
FROM Prices
WHERE SiteId = 10
) r
LEFT JOIN Products p ON p.stockcode = r.stockcode
) x
LEFT JOIN zExtract z ON z.stockcode = x.stockcode
WHERE z.stockcode IS NULL
AND x.type = 4
--first off your doing a left join on the inner select yet forcing the product table to have a value of 4 which kinda defeats the purpose of using a left join.
--second any constraints on the product table ( x.type = 4 ) should exist in the inner most selects where. like below. Otherwise your probably joining and processing many null records which i assume are a waste of time.
SELECT COUNT(*)
FROM
(
SELECT r.stockcode, p.type FROM
(
SELECT DISTINCT stockcode
FROM Prices
WHERE SiteId = 10
) r
LEFT JOIN Products p ON p.stockcode = r.stockcode
Where p.type = 4
) x
LEFT JOIN zExtract z ON z.stockcode = x.stockcode
WHERE z.stockcode IS NULL
December 22, 2009 at 9:18 am
OK, so I updated the statistics for Prices and Products and the query now worked. Thanks a lot.
I haven't understood why updating the statistics changed everything though.
I'll give some information which was requested, maybe it will help.
1. The Type field in Products table is NOT nullable.
2. As some pointed out, the left join is irrelevant in the case where I have the condition [font="Courier New"]AND x.type = 4[/font]. This is true, but I was also running the query without this condition and the only condition was [font="Courier New"]WHERE z.stockcode IS NULL[/font]. When I added the second condition I did not change LEFT JOIN to INNER JOIN.
3. There is no foreign key constraint on StockCode column of Prices table pointing to StockCode column of Products table?
4. I have never checked for fragmentation so I googled a bit and used this query
SELECT * FROM sys.dm_db_index_physical_stats (
DB_ID(N'RSWEB'), OBJECT_ID(N'PRODUCTS'), NULL, NULL , NULL);
database_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
----------- ----------- ----------- ---------------- ------------------------------------------------------------ ------------------------------------------------------------ ----------- ----------- ---------------------------- -------------------- -------------------------- -------------------- ------------------------------ -------------------- -------------------- -------------------------- ------------------------ ------------------------ ------------------------ ----------------------
6 356196319 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 52.5240709079456 5879 1.8902874638544 11113 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 39.3288402134232 2870 2.4815331010453 7122 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 3 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 28.150289017341 493 3.50912778904665 1730 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 4 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 75.1143292682927 1994 1.31594784353059 2624 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 5 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 24.8623131392604 320 3.971875 1271 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 6 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 1.10330992978937 13 76.6923076923077 997 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 7 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 22.4471021159154 248 4.38306451612903 1087 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 16 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 0.929752066115702 10 96.8 968 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 21 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 0 5 193.6 968 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 22 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 1.84049079754601 21 46.5714285714286 978 NULL NULL NULL NULL NULL NULL NULL NULL
6 356196319 23 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 17.5919250180245 274 5.06204379562044 1387 NULL NULL NULL NULL NULL NULL NULL NULL
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply