Timeline for Data retrieving is taking more than 4hrs to fetch 400K records
Current License: CC BY-SA 4.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 21, 2023 at 4:45 | vote | accept | Python coder | ||
| Aug 2, 2023 at 6:58 | comment | added | Python coder | As this post looks diverted to a different question, I have posted a new question here with details. | |
| Aug 2, 2023 at 5:18 | comment | added | Python coder | The query is running for more than 18hrs, and it's still running, I was able to get the execution plan from running query, can you please check that too, if it helps? updated the post with details. | |
| Aug 1, 2023 at 18:51 | comment | added | Python coder | Thanks J.D. Looking for your answer. | |
| Aug 1, 2023 at 18:48 | comment | added | J.D. | @Pythoncoder Yea a couple seconds (or under a minute with a cold cache) makes more sense. That's normal. I don't think you have any root issues affecting multiple queries then. It seems like your issue is just your main query and how it's coded. I'll try to advise with details on how to tune it when I get a chance. (Also be careful not to clear the cache in a production server unless you really intended to.) | |
| Aug 1, 2023 at 18:32 | comment | added | Python coder |
Added wait_info, it took 36 seconds after clearing cache as mentioned here
|
|
| Aug 1, 2023 at 18:23 | comment | added | Python coder |
Surprisingly, the select count(1)... is getting executed in 2-3 seconds, not able to get wait_info because of low execution time, here is the actual execution plan. As no one uses dev database in night time, it may be getting executed fast because of less load? but my actual cte query is still running from more than 7 hours.
|
|
| Aug 1, 2023 at 18:12 | comment | added | J.D. |
@Pythoncoder "how to check the wait types?" - You can use sp_WhoIsActive to see a list of the running queries on your server and their wait types (wait_info column). If you run that a few times while the basic example query is running, write down the names of the wait types and please add them to your Post. Also, the execution plan you provided is the estimated execution plan, but if you can obtain the actual execution plan for your basic example query, that would be a lot more helpful.
|
|
| Aug 1, 2023 at 18:06 | comment | added | Python coder | Can you share the possible reason and suggestions for that, so that I can try out them? | |
| Aug 1, 2023 at 18:02 | comment | added | Python coder |
Thanks again for your quick response, here is the execution plan for select count(1) from table where col_filter >= 'number' , how to check the wait types? Sure I will update the post as well.
|
|
| Aug 1, 2023 at 17:55 | comment | added | J.D. |
@Pythoncoder well on quick glance, I can see why your main query could be problematic for the tables at the sizes they are. The query isn't written in the most performant way and likely needs to be tuned. But I'd be even more curious in seeing the actual execution plan and wait types for your simple example query select count(1) from table where col_filter >= 'number' which shouldn't be taking hours either. If there's a root problem that can be solved for that, it'll likely positively affect your main query too. Btw you can put updated information in your original Post so others see it too.
|
|
| Aug 1, 2023 at 16:03 | comment | added | Python coder |
This query runs for more than 16hrs, so I started debugging from base table1, where table1 contains 1.5M, table2 contains 130M, table3 contains 300M rows. Thanks for your help so far :)
|
|
| Aug 1, 2023 at 16:03 | comment | added | Python coder |
Thanks for the answer. This table is part of other data fetch query, which contains, cte, inner joins and then using cte as base table which perform group by operations, the table which I posted in post is the base table inside cte, as it's taking long time even for count, I thought it would be good start to debug. Here is the full query with execution plan.
|
|
| Aug 1, 2023 at 12:50 | history | edited | J.D. | CC BY-SA 4.0 |
added 9 characters in body
|
| Aug 1, 2023 at 12:42 | history | answered | J.D. | CC BY-SA 4.0 |