Query very slow
-
My query is as below
SELECT JS.Code, FC.Name, JS.CompanyCode FROM JOBS JS LEFT OUTER JOIN CUSTOMERS FC ON JS.CCode = FC.CCode and JS.GNumb = JS.GNumb
ORDER BY FS.sdate, FS.Code, FC.Name
Table JOBS contains 3,34,516 rows , Table Customers contains 60,416 rows
It takes me 6 seconds to retrieve data (3,34,516 rows) with out order by and 15 seconds with Order by clause
I have done indexing on sdate, code, groupnumb field. Please help out if we can do something more to get the result set faster
-
Hi Krishna,
Please check once the order by columns with their alias names.
FS.sdate, FS.Code
Lucky -
My query is as below
SELECT JS.Code, FC.Name, JS.CompanyCode FROM JOBS JS LEFT OUTER JOIN CUSTOMERS FC ON JS.Code = FC.CCode and JS.GNumb = FC.GNumb
ORDER BY JS.sdate desc , JS.Code, FC.Name
Table JOBS contains 3,34,516 rows , Table Customers contains 60,416 rows
It takes me 6 seconds to retrieve data (3,34,516 rows) with out order by and 15 seconds with Order by clause
I have done indexing on sdate, code, groupnumb field. Please help out if we can do something more to get the result set faster
-
335K rows in 15 seconds isn't really slow. What are the server specs. What is the execution plan?
-
The query does not have any filter criteria specified at all i.e. no where clause. Is there indeed a requirement to return all the 335k records back ? What does the application do after getting such a large record-set back ? An answer to that might help in determining the next course of action.
In the abscene of the execution plan and the type of indexes, I can only guess that this must be doing an index scan operation and a clustered index scan on a not null column is similar to scanning the entire table itself.
-
3.3 Million rows in 15 seconds isn't slow, it's screaming!
If you're getting that much data to your desktop that quickly I'd be pleased as punch.
Joe
-
I'd be happy with 3.3 million rows in 15 Seconds also. But Krishna is only getting 10 % of that 334,516 (it's amazing what a misplaced comma can do to the eyes) so the query isn't all that quick... But without the execution plan and server specs it's impossible to say how to speed it up.
Ged
-
My Executions Plan
|--Sort(ORDER BY
[JS].[Sdate] DESC, [JS].[Code] ASC))|--Hash Match(Right Outer Join, HASH
[FC].[CCode], [FC].[GNumb])=([JS].[CCode], [JS].[GNumb]), RESIDUAL[JS].[CCode]=[FC].[CCode] AND [JS].[GNumb]=[FC].[GNumb]))|--Clustered Index Scan(OBJECT
[NEXT_ALL].[dbo].[CUSTOMERS].[PK_CUSTOMERS_CCode] AS [FC]))|--Clustered Index Scan(OBJECT
[NEXT_ALL].[dbo].[JOBS].[PK_JOBS_Code] AS [JS]))Server Specifications
P VI with 512 RAM
Sharma, i have done Clustered Index for code and CCode and other are non- clustered indexes
-
Still doesn't answer the question of why you need ot have all those records sent to somewhere. Also that doesn't see awfully slow to me. I tried running a left join on one of my main tables and it runs in 8 secs for 250k rows (bit faster server than yours). The network can greatly affect that number by the way.
-
RGR'us
We need to display all these rows in a LOOK UP screen where the user can select the code , we are using date ranges to display the result set but in some cases user can give date range where all the rows fall under this...
My Server Spec
Pentium 4 not P VI
-
Ya we figured the part about the P 6
. Where is the between datestart and dateend condition then??
It should be included in the query all the time.
-
RGR'us
Check the below query this returns me 3,34,516 rows
SELECT JS.Code, FC.Name, JS.CompanyCode FROM JOBS JS LEFT OUTER JOIN CUSTOMERS FC ON JS.Code = FC.CCode and JS.GNumb = FC.GNumb
WHERE JS.sdate >='1/1/1970' and JS.sdate <= '12/31/2005'
ORDER BY JS.sdate desc , JS.Code, FC.Name
-
Could you please clear this out? Is it 335K or 3.3 M rows?
-
335K
-
How fast is it to fetch only lets say one month of data?
Can you move the order by on the client?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply