CODEWITHSUNDEEP
sqldatabasedata-warehouse
rgksugan
rgksugan

Reputation: 3582

What are the factors that affect the time taken to run a SQL on a database?

I have a query that runs on a data warehouse. I ran the report last month. It gave me some results in say x minutes. The same report when run on the same database without any modifications to the database returns the same results but in y minutes now.

y>x. The difference between the time is so large.

The amount of data and the indexes are also the same. There is no difference in them.

Now clients ask for me for a reason for this. What are the possible reasons for this?

Upvotes: 1

Views: 129

Answers (3)

Whimsical
Whimsical

Reputation: 6355

Here are a few:

  1. The amount of data in the warehouse has changed.
  2. Indexes might have been modified.
  3. Your warehouse is split across different servers and there is connectivity lag between them...
  4. Your database server is processing something else as well due to which it has lesser memory and cpu for ur reports to run.

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

You leave a lot of questions open

  • is the database running on a dedicated server.
  • do you run the reports from clients or directly on the server.
  • have there been changes to the phyisical network, have some settings been changed.
  • did they (by accident) change the protocol to communicate with the server (tcp, named-pipes, ...)
  • have you tried defragmenting
  • have you rebooted the server
  • do you have an execution plan before and after

Upvotes: 2

WW.
WW.

Reputation: 24311

Most likely the query plan has changed. Some minor difference in data has pushed the query optimisers calculations onto a new, less optimal plan.

Upvotes: 2

Related Questions