?
Solved

Why this simple SQL query takes too long time to be executed?!!

Posted on 2008-11-11
18
Medium Priority
?
3,249 Views
Last Modified: 2012-06-21
Hi, do someone know why the execution time time for the following SQL query takes more that 25 second?!  By the way the two tables contains only 10 records.
I'm sure the reason is the Convert function but why? and how to accelerate this SQL statement?
SELECT 'US' + CONVERT(varchar,[user].user_id ) AS id,[user].Full_Name AS item FROM [user] UNION SELECT 'GR' + CONVERT(varchar,gr.group_id ) AS id,gr.Name AS item FROM [group] AS gr;

Open in new window

0
Comment
Question by:Abdu_Allah
  • 7
  • 6
  • 3
  • +1
18 Comments
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 120 total points
ID: 22932542
How long does the sql take if you remove the convert function?

If it takes the same length of time then it is your database or machine that is the problem, not your sql.

You could also change the UNION to a UNION ALL which will avoid a sort.  UNION retrives distinct rows from the two statements, whereas UNION ALL returns all rows.  Of course, if you need to return distinct rows from each query then you need to use UNION, however, I notice that the prefix (US/GR) is different in both queries so I'm making an assumption.

As you suggest, I would expect a statement retrieving 20 rows to return values instantly.
0
 
LVL 8

Assisted Solution

by:eszaq
eszaq earned 240 total points
ID: 22932586
I am not sure what result you atrying to achieve, but... Is there particular reason for using CONVERT function? Why don't you just select three columns in your query? Something like this:
SELECT 'US' as id_type, [user].user_id as id,[user].Full_Name AS item
FROM [user]
UNION
SELECT 'GR' as id_type, gr.group_id AS id, gr.Name AS item
FROM [group] AS gr;

You can always concatinate string on your application level for output similar to this:
output = "<tr><td>" & id_type & id & "</td><td>" & item & "</td></tr>"
0
 
LVL 3

Author Comment

by:Abdu_Allah
ID: 22938437
>How long does the sql take if you remove the convert function?
Nothing, less than one second. Obvoiusly the reason is from convert function, so do you have another alternative?
>Is there particular reason for using CONVERT function?
Yes of course I want to concatinate the characters 'GR' and 'US' with the id number.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 3

Author Comment

by:Abdu_Allah
ID: 22948751
Could someone try it on his own machine please? I want to make sure it's not from my own machine?
0
 
LVL 6

Accepted Solution

by:
Gugro earned 1640 total points
ID: 22951701
tried it on my SQL 2005 Server : Runtime < 1 sec giving a resultset of 20 rows

0
 
LVL 8

Assisted Solution

by:eszaq
eszaq earned 240 total points
ID: 22951856
Gugro:
did you use your own tables to test the query or created tables with the same names as in  Abdu_Allah's query?
I wonder if problem is caused by both of table-names (user and group) being reserved keywords.
0
 
LVL 3

Author Comment

by:Abdu_Allah
ID: 22955540
>tried it on my SQL 2005 Server : Runtime < 1 sec giving a resultset of 20 rows
So the problem is at my side! thank you.
>I wonder if problem is caused by both of table-names (user and group) being reserved keywords
I do not think so.
0
 
LVL 8

Assisted Solution

by:eszaq
eszaq earned 240 total points
ID: 22955565
try to export your data into new tables with different names and run query against those.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 22958069
The problem is unlikely to be caused by the table names.  The first thing I asked him to do was run the query without using the convert function and everything ran okay.

Is there a concept of a database language and a client side language in SQL Query (my expertise is within Oracle).  If so, is it possible that client side settings are different to the database settings, thereby causing the convert routine to do more work that it needs to?
0
 
LVL 6

Assisted Solution

by:Gugro
Gugro earned 1640 total points
ID: 22959088
I created two tables with the names user and group and used EXACTLY the query string Abdu Allah posted.

May I ask where you are running the query ?
- On the server or on the client ?
-  in  SQL Server Management Studio or in an application ?
- could you try to get the execution plan of the sql server ?
0
 
LVL 3

Author Comment

by:Abdu_Allah
ID: 22960759
I run this query in both  SQL Server Management Studio and application and i takes the same time(more than 25 second).
>could you try to get the execution plan of the sql server ?
Sorry, what is execution plan of the sql server ?
0
 
LVL 6

Assisted Solution

by:Gugro
Gugro earned 1640 total points
ID: 22974053
InSQL Server Management Studio :

SET SHOWPLAN_TEXT ON
go
SELECT 'US' + CONVERT(varchar,[user].user_id ) AS id,[user].Full_Name AS item FROM [user] UNION SELECT 'GR' + CONVERT(varchar,gr.group_id ) AS id,gr.Name AS item FROM [group] AS gr;

Gives me the simple plan:
  |--Sort(DISTINCT ORDER BY:([Union1009] ASC, [Union1010] ASC))
       |--Concatenation
            |--Compute Scalar(DEFINE:([Expr1004]='US'+CONVERT(varchar(30),[ZwickTest].[dbo].[user].[user_id],0)))
            |    |--Table Scan(OBJECT:([ZwickTest].[dbo].[user]))
            |--Compute Scalar(DEFINE:([Expr1008]='GR'+CONVERT(varchar(30),[ZwickTest].[dbo].[group].[group_id] as [gr].[group_id],0)))
                 |--Table Scan(OBJECT:([ZwickTest].[dbo].[group] AS [gr]))
0
 
LVL 3

Author Comment

by:Abdu_Allah
ID: 22974853
Here is what I got:
  |--Sort(DISTINCT ORDER BY:([Union1007] ASC, [Union1008] ASC))
       |--Concatenation
            |--Compute Scalar(DEFINE:([Expr1003]='US'+CONVERT(varchar(30),[VistaFileExplorer].[dbo].[User].[User_ID],0)))
            |    |--Clustered Index Scan(OBJECT:([VistaFileExplorer].[dbo].[User].[PK_User]))
            |--Compute Scalar(DEFINE:([Expr1006]='GR'+CONVERT(varchar(30),[VistaFileExplorer].[dbo].[Group].[Group_ID] as [gr].[Group_ID],0)))
                 |--Index Scan(OBJECT:([VistaFileExplorer].[dbo].[Group].[IX_Group] AS [gr]))
0
 
LVL 6

Assisted Solution

by:Gugro
Gugro earned 1640 total points
ID: 22975151
So my SQL server is using a Table Scan, because there are only 10 Rows in then table.
BUT your SQL server uses an Index Scan, so I believe there are a lot more rows in your Table .

Could you please try:

dbcc Show_Statistics ('user', PK_User)   WITH STAT_HEADER
dbcc Show_Statistics ('group', IX_Group)  WITH STAT_HEADER

0
 
LVL 3

Author Comment

by:Abdu_Allah
ID: 22975278
I tried them the numbers of rows is 1
0
 
LVL 6

Assisted Solution

by:Gugro
Gugro earned 1640 total points
ID: 22975410
Please post the output of the both commands. It should read like that:

PK_user      Nov 17 2008  2:03PM      10      10      6      1      4      NO
IX_group      Nov 17 2008  2:05PM      10      10      6      1      4      NO
0
 
LVL 3

Author Comment

by:Abdu_Allah
ID: 22976071
Ok
PK_User     Nov   2 2008  8:00PM      1     1     1       0      4          NO
IX_Group     Nov  2 2008  3:32PM       1     1     1       0     18         YES
0
 
LVL 6

Assisted Solution

by:Gugro
Gugro earned 1640 total points
ID: 22977620
You were telling us : "... By the way the two tables contains only 10 records."
But Show_Statistics is telling us there is only ONE row in each table, and WHY should the query analyzer use an index if there is only ONE row in the table. I am really puzzled :-((

so lets try:

UPDATE STATISTICS [user]
go
UPDATE STATISTICS [group]
go

and the again:
SET SHOWPLAN_TEXT ON
....
......


dbcc Show_Statistics ('user', pk_user) WITH STAT_HEADER
dbcc Show_Statistics ('group', IX_Group) WITH STAT_HEADER
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question