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

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

LVL 3
Abdu_AllahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MilleniumaireCommented:
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
eszaqCommented:
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
Abdu_AllahAuthor Commented:
>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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Abdu_AllahAuthor Commented:
Could someone try it on his own machine please? I want to make sure it's not from my own machine?
0
GugroCommented:
tried it on my SQL 2005 Server : Runtime < 1 sec giving a resultset of 20 rows

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eszaqCommented:
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
Abdu_AllahAuthor Commented:
>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
eszaqCommented:
try to export your data into new tables with different names and run query against those.
0
MilleniumaireCommented:
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
GugroCommented:
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
Abdu_AllahAuthor Commented:
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
GugroCommented:
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
Abdu_AllahAuthor Commented:
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
GugroCommented:
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
Abdu_AllahAuthor Commented:
I tried them the numbers of rows is 1
0
GugroCommented:
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
Abdu_AllahAuthor Commented:
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
GugroCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.