Why Is Running a Query on SQL Azure So Much Slower

Why is running a query on SQL Azure so much slower?

This is first and foremost a question of performance. You are dealing with a poorly performing code on your part and you must identify the bottleneck and address it. I'm talking about the bad 2 seconds performance now. Follow the guidelines at How to analyse SQL Server performance. Once you get this query to execute locally acceptable for a web app (less than 5 ms) then you can ask the question of porting it to Azure SQL DB. Right now your trial account is only highlighting the existing inefficiencies.

After update

...
@iddepartment int
...
iddepartment='+convert(nvarchar(max),@iddepartment)+'
...

so what is it? is the iddepartment column an int or an nvarchar? And why use (max)?

Here is what you should do:

• parameterize @iddepartment in the inner dynamic SQL
• stop doing nvarchar(max) conversion. Make the iddepartment and @iddertment types match
• ensure indexes on iddepartment and all idkpis

Here is how to parameterize the inner SQL:

set @sql =N'
Select * from (
select kpiname, target, ivalues, convert(decimal(18,2),day(idate)) as iDay   
from kpi
inner join kpivalues on kpivalues.idkpi=kpi.idkpi
inner join kpitarget on kpitarget.idkpi=kpi.idkpi
inner join departmentbscs on departmentbscs.idkpi=kpi.idkpi
where iddepartment=@iddepartment
group by kpiname,target, ivalues,idate)x
pivot
(
     avg(ivalues)
    for iDay in (' +@columnName + N')
) p'

execute sp_executesql @sql, N'@iddepartment INT', @iddepartment;

The covering indexes is, by far, the most important fix. That obviously requires more info than is here present. Read Designing Indexes including all sub-chapters.

As a more general comment: this sort of queries befit columnstores more than rowstore, although I reckon the data size is, basically, tiny. Azure SQL DB supports updateable clustered columnstore indexes, you can experiment with it in anticipation of serious data size. They do require Enterprise/Development on the local box, true.

SQL Azure - very slow compared to localhost database

I've used Azure now for the last many years, and my original question is pretty much solved.

My main take-aways after dealing with Azure databases for a while:

• It's extremely important that your application and database is placed in the same region. If not, then you will have a slow application. Recently I had an API and app running on two different regions - it took ~1 second for every response.. After moving it to same, it was instant
• If your application has a high load, it's often a good idea to upgrade. This happens earlier than you might expect
• Pick the nearest region - it really matters

Why is this Azure SQL query extremely slow?

There are couple of problems here:

• Unknown amount of columns you select from your table
• Like you said, LIKE statement.

To do proper analysis, it'd be best to see an execution plan, bust this is quite common problem, so I'd say that there's two things that might be happening:

• A full table scan based on your condition
• Nonclustered index seek based on companyid and languagecode and a filter predicate based on noos14 and a key lookup operator, which retrieves all other columns from table that are not in index.

So in order to solve your issue, just change your index to include all required columns and replace SELECT * with an explicit list of columns you need.

FLASK AZURE SQL DB - Queries running very slowly

I actually managed to find a solution inspired by CairoCoders.
The problem was that the query returned all results at once on the page.

Here is a was to do it on with AJAX:

Flask AJAX Datatables

or using pagination (SQLAlchemy):

Flask SQLAlchemy pagination

---

Related Topics