SQL Statement Runs Slow

Question

Post reply

SQL Statement Runs Slow

Allen Cui-55137

SSC Guru

Points: 51650
More actions
November 11, 2002 at 9:35 am

#158221

SQL Statement captured from Crystal Report or Access application from Profiler runs much slow in Query Analyzer. Any ideas and suggestions? Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mkumari Hall of Fame Points: 3498 More actions · Answer 1

Can you give more details about the kind server/servers which you are using.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

Running SQL Server 2000 with SP2 on Windows 2000 advance server SP2 with 2 CPUs machine and 4GB Ram. Client runs Crystal Report application to connect to SQL Server via ODBC.

NPeeters SSChampion Points: 12535 More actions · Answer 3

Does it also run slow in CR or Access?

Can you give us the query?

Maybe post an execution plan too.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

The query runs much faster than in Query Analyzer.

SELECT SAMPLE.RECD_DATE, SAMPLE.DATE_REVIEWED, SAMPLE.SAMPLING_POINT, SAMPLE.CHANGED_ON, SAMPLE.LAB_, SAMPLE.GROUP_NAME, LOT.LOT_NUMBER, LOT.LOT_NAME, TEST.ANALYSIS, LOT_SAMPLING_POINT.DISPOSITION, LOT_SAMPLING_POINT.DISPOSITION_DATE, PRODUCT.DESCRIPTION, RESULT.STATUS FROM LabWareDev.dbo.SAMPLE SAMPLE INNER JOIN LabWareDev.dbo.TEST TEST ON SAMPLE.SAMPLE_NUMBER = TEST.SAMPLE_NUMBER INNER JOIN LabWareDev.dbo.LOT LOT ON SAMPLE.LOT = LOT.LOT_NUMBER INNER JOIN LabWareDev.dbo.PRODUCT PRODUCT ON LOT.PRODUCT = PRODUCT.NAME INNER JOIN LabWareDev.dbo.RESULT RESULT ON TEST.TEST_NUMBER = RESULT.TEST_NUMBER INNER JOIN LabWareDev.dbo.LOT_SAMPLING_POINT LOT_SAMPLING_POINT ON LOT.LOT_NUMBER = LOT_SAMPLING_POINT.LOT_NUMBER AND LOT.LOT_NAME = LOT_SAMPLING_POINT.LOT_NAME AND LOT.SAMPLING_POINT = LOT_SAMPLING_POINT.SAMPLING_POINT AND LOT.PRODUCT = LOT_SAMPLING_POINT.PRODUCT AND LOT.PRODUCT_VERSION = LOT_SAMPLING_POINT.PRODUCT_VERSION AND LOT.PRODUCT_GRADE = LOT_SAMPLING_POINT.PRODUCT_GRADE WHERE TEST.ANALYSIS <> 'CS-SPLWTQ' AND TEST.ANALYSIS <> 'YY_SPLSTAT' AND RESULT.STATUS = 'A' ORDER BY SAMPLE.LAB_ ASC, SAMPLE.SAMPLING_POINT ASC, LOT.LOT_NUMBER ASC

|--Parallelism(Gather Streams, ORDER BY:([SAMPLE].[LAB_] ASC, [SAMPLE].[SAMPLING_POINT] ASC, [LOT].[LOT_NUMBER] ASC))

|--Sort(ORDER BY:([SAMPLE].[LAB_] ASC, [SAMPLE].[SAMPLING_POINT] ASC, [LOT].[LOT_NUMBER] ASC))

|--Nested Loops(Inner Join, OUTER REFERENCES:([LOT].[PRODUCT_GRADE], [LOT].[PRODUCT_VERSION], [LOT].[SAMPLING_POINT], [LOT].[LOT_NUMBER], [LOT].[LOT_NAME], [PRODUCT].[NAME]) WITH PREFETCH)

|--Nested Loops(Inner Join, OUTER REFERENCES:([LOT].[PRODUCT]) WITH PREFETCH)

| |--Hash Match(Inner Join, HASH:([LOT].[LOT_NUMBER])=([SAMPLE].[LOT]), RESIDUAL:([SAMPLE].[LOT]=[LOT].[LOT_NUMBER]))

| | |--Bitmap(HASH:([LOT].[LOT_NUMBER]), DEFINE:([Bitmap1007]))

| | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([LOT].[LOT_NUMBER]))

| | | |--Clustered Index Scan(OBJECT:([LabWareDev].[dbo].[LOT].[LOT_LOT_NUMBER185] AS [LOT]))

| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([SAMPLE].[LOT]), WHERE:(PROBE([Bitmap1007])=TRUE))

| | |--Hash Match(Inner Join, HASH:([TEST].[SAMPLE_NUMBER])=([SAMPLE].[SAMPLE_NUMBER]), RESIDUAL:([SAMPLE].[SAMPLE_NUMBER]=[TEST].[SAMPLE_NUMBER]))

| | |--Bitmap(HASH:([TEST].[SAMPLE_NUMBER]), DEFINE:([Bitmap1006]))

| | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([TEST].[SAMPLE_NUMBER]))

| | | |--Hash Match(Inner Join, HASH:([RESULT].[TEST_NUMBER])=([TEST].[TEST_NUMBER]), RESIDUAL:([TEST].[TEST_NUMBER]=[RESULT].[TEST_NUMBER]))

| | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([RESULT].[TEST_NUMBER]))

| | | | |--Table Scan(OBJECT:([LabWareDev].[dbo].[RESULT] AS [RESULT]), WHERE:([RESULT].[STATUS]='A'))

| | | |--Hash Match(Inner Join, HASH:([TEST].[TEST_NUMBER])=([TEST].[TEST_NUMBER]))

| | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([TEST].[TEST_NUMBER]))

| | | | |--Index Seek(OBJECT:([LabWareDev].[dbo].[TEST].[TEST_ANALYSIS] AS [TEST]), SEEK:([TEST].[ANALYSIS] < 'YY_SPLSTAT' OR [TEST].[ANALYSIS] > 'YY_SPLSTAT'), WHERE:([TEST].[ANALYSIS]<'CS-SPLWTQ' OR [TEST].[ANALYSIS]>'CS-SPLWTQ') ORDERED FORWARD)

| | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([TEST].[TEST_NUMBER]))

| | | |--Index Scan(OBJECT:([LabWareDev].[dbo].[TEST].[TEST_SAMPLE_NUM344] AS [TEST]))

| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([SAMPLE].[SAMPLE_NUMBER]), WHERE:(PROBE([Bitmap1006])=TRUE))

| | |--Clustered Index Scan(OBJECT:([LabWareDev].[dbo].[SAMPLE].[SAMPLE_SAMPLE_N296] AS [SAMPLE]))

| |--Clustered Index Seek(OBJECT:([LabWareDev].[dbo].[PRODUCT].[PRODUCT_NAME_VE209] AS [PRODUCT]), SEEK:([PRODUCT].[NAME]=[LOT].[PRODUCT]) ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:([LabWareDev].[dbo].[LOT_SAMPLING_POINT].[LOT_SAMPLING_PO196] AS [LOT_SAMPLING_POINT]), SEEK:([LOT_SAMPLING_POINT].[SAMPLING_POINT]=[LOT].[SAMPLING_POINT] AND [LOT_SAMPLING_POINT].[PRODUCT_GRADE]=[LOT].[PRODUCT_GRADE] AND [LOT_SAMPLING_POINT].[LOT_NUMBER]=[LOT].[LOT_NUMBER]), WHERE:(([LOT].[PRODUCT_VERSION]=[LOT_SAMPLING_POINT].[PRODUCT_VERSION] AND [PRODUCT].[NAME]=[LOT_SAMPLING_POINT].[PRODUCT]) AND [LOT].[LOT_NAME]=[LOT_SAMPLING_POINT].[LOT_NAME]) ORDERED FORWARD)

Mkumari Hall of Fame Points: 3498 More actions · Answer 5

I am not sure if this would help you.In SQL Server 7.0 when I used to run the stored procedure/query in the Query analyser by selecting the results in text it used take lot of time but when I select results in grid it used to take almost the same time as the application/crystal reports used to take.So, what I think is the query is trying to use the local machines resources from which the query is executed and that's why it is runing slow.