asked on

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

SOLUTION

Milleniumaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

eszaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abdu_Allah

ASKER

>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.

Abdu_Allah

ASKER

Could someone try it on his own machine please? I want to make sure it's not from my own machine?

ASKER CERTIFIED SOLUTION

Gugro

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

eszaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abdu_Allah

ASKER

>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.

SOLUTION

eszaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Milleniumaire

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?

SOLUTION

Gugro

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abdu_Allah

ASKER

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 ?

SOLUTION

Gugro

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abdu_Allah

ASKER

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]))

SOLUTION

Gugro

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abdu_Allah

ASKER

I tried them the numbers of rows is 1

SOLUTION

Gugro

membership