SQL Performance issue

Questions and answers regarding general SQL and backend databases

SQL Performance issue

Postby Peter de Groot » Wed Oct 19, 2016 11:34 am

Hi all,

When the statement below is executed the execution time is, as you can see, 00:22:643. In this statement I use for 'Not Equals' <>

slow.png
slow.png (39.92 KiB) Viewed 4216 times


But the statement below is identical to the one above for one exception, I use != for 'Not Equals', the execution time is 00:00:128 :shock:

fast.png
fast.png (39.61 KiB) Viewed 4216 times


Can someone explain why the first statement takes 22 seconds longer to execute than the second statement?

We use MS-SQL server 2012, used Servoy version is 7.4.3 - build 2036


Regards,

Peter
User avatar
Peter de Groot
 
Posts: 215
Joined: Thu Jan 10, 2008 8:38 pm
Location: Not sure...

Re: SQL Performance issue

Postby ROCLASI » Wed Oct 19, 2016 11:48 am

Hi Peter,

The <> operator is ANSI SQL (i.e. standard SQL) and != is not. But most DB vendors support both syntaxes.
Now why one is faster (in MSSQL) than the other is a good question. I suggest you do an EXPLAIN on both queries and see what it actually is doing with it.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: SQL Performance issue

Postby kwpsd » Wed Oct 19, 2016 6:43 pm

If I am not mistaken, EXPLAIN is a mySQL construct. For MS SQL, you have to use an execution plan. In MS SQL Management Studio, run the following command by itself:

Code: Select all
SET SHOWPLAN_ALL ON


Then, run the SQL statement in question as you normally would. Instead of executing the SQL statement, you will be presented with information as to how the statement is constructed and executed. When done, don't forget to turn off the execution plan:

Code: Select all
SET SHOWPLAN_ALL OFF


I hope this helps
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: SQL Performance issue

Postby Peter de Groot » Thu Oct 20, 2016 7:39 am

Hi,

I will ask our DBA to run a execution plan.
When run both versions of the statement in DB Visualizer I get almost the same execution time

statement1.png
statement1.png (45.63 KiB) Viewed 4166 times


statement2.png
statement2.png (46.36 KiB) Viewed 4166 times


Regards,

Peter
User avatar
Peter de Groot
 
Posts: 215
Joined: Thu Jan 10, 2008 8:38 pm
Location: Not sure...

Re: SQL Performance issue

Postby ROCLASI » Thu Oct 20, 2016 7:49 am

Hi Peter,

So in DB Vis you see about the same execution times...that is weird. That almost suggests that there is something else going on like waiting for a table lock or something (which MSSQL is prone to).
Those slow times did you see those consistently or did you just happen to spot one in your query logging?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests