1

I am trying to execute a raw sql query with connections[].cursor() in a Django app that connects to SQL server. The query executes much faster (<1s) when I provide the actual vlaues in the query string.

from django.db import connections
     with connections['default'].cursor() as cursor:
        cursor.execute("""                   
                        select c.column1 as c1
                        , ve.column2 as c2
                        from view_example c
                        left join view_slow_view ve on c.k1 = ve.k2
                        where c.column_condition = value_1 and c.column_cd_2 = value2
                        """)
        result = dictfetchall(cursor)

But when I provide the values as params in the cursor.execute() method, the query becomes much slower (2 minutes).

from django.db import connections
     with connections['default'].cursor() as cursor:
        cursor.execute("""                   
                        select c.column1 as c1
                        , ve.column2 as c2
                        from view_example c
                        left join view_slow_view ve on c.k1 = ve.k2
                        where c.column_condition = %s and c.column_condition_2 = %s
                        """, [value_1, value_2])
        contracts_dict_lst = dictfetchall(cursor)

I should also mention that the query is actually slow when executed on SSMS ONLY IF a condition is NOT provided:

 where c.column_condition = value_1 and c.column_cd_2 = value2

It is as if when Django sends the query, it is executed without the parameters (hence the long response time) and then the parameters are provided so the result is filtered.

The values in question are provided by the user, so they change and have to be passed as params and not directly in the query to avoid sql injection. The query is also much more complex than the example given above and doesn't map cleanly to a model so I have to use connection[].cursor()

Bellatrix
  • 29
  • 1
  • 8

2 Answers2

2

This is probably parameter sniffing issue. If that's the case, there are couple of solutions. The easiest solution is using query hint.

Option 1:

from django.db import connections
     with connections['default'].cursor() as cursor:
        cursor.execute("""                   
                        select c.column1 as c1
                        , ve.column2 as c2
                        from view_example c
                        left join view_slow_view ve on c.k1 = ve.k2
                        where c.column_condition = %s and c.column_condition_2 = %s
                        OPTION(RECOMPILE) -- add this line to your query
                        """, [value_1, value_2])
        contracts_dict_lst = dictfetchall(cursor)

Option 2:

from django.db import connections
     with connections['default'].cursor() as cursor:
        cursor.execute(""" 
                        declare v1 varchar(100) = %s  -- declare variable and use them
                        declare v2 varchar(100) = %s
              
                        select c.column1 as c1
                        , ve.column2 as c2
                        from view_example c
                        left join view_slow_view ve on c.k1 = ve.k2
                        where c.column_condition = v1 and c.column_condition_2 = v2
                        """, [value_1, value_2])
        contracts_dict_lst = dictfetchall(cursor)

This is a good link for more reading.

FLICKER
  • 6,439
  • 4
  • 45
  • 75
  • This did not solve my problem. The response time is still too long. – Bellatrix Nov 12 '20 at 09:37
  • 1
    I provided second option, please try that one too. – FLICKER Nov 12 '20 at 15:13
  • 1
    Thank you very much. Just declaring the variables ended up doing the trick (even without OPTION(RECOMPILE) ). – Bellatrix Nov 13 '20 at 08:29
  • Glad it helped. Right, when using variable you don't need to use option(recompile). I'll edit my answer. – FLICKER Nov 13 '20 at 15:06
  • OPTION(RECOMPILE) did not improve my query performance at all, but second option did. I thought I was going crazy. Same query that I executed from SSMS was fast, but from python it was much slower. Ended up with 2200x faster query (It's a huge table near 550M records). – DoubleM Apr 15 '23 at 21:16
0
msql = "Select count(1) as Bil from customer where AcctNo = '" + ACCTNO +"'"

Don't execute with parameter for AcctNo.

Below will run slower:

result = cursor1.execute('Select count(1) as Bil from customer where AcctNo = ?', ACCTNO)
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Che Shamsul
  • 1