which query is better - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

which query is better

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2009-03-10 : 13:50:13

Hi
I received the next query, in this query they made some select statements and the result of them is joined by the ID_SOLICITUD, they also can be joined by the ID_PROMOTORIA, but the person that made the query put this in a Where statement on each query.

In your opinion which of the next options is better?

1) Query that I received

SELECT *
FROM
(
SELECT WBI_NOMBRE_PROCESO
,WBI_ESTATUS
,cast(WBI_ID_SOLICITUD as bigint)WBI_ID_SOLICITUD
,WBI_FEC_INICIO
FROM WF_BITACORAS
WHERE WBI_CVE_PROMOTORIA = @ID_PROMO
AND WBI_NOMBRE_PROCESO IN ('Servicios_V2','LotesValijas')
AND WBI_ESTATUS = @ESTATUS
AND WBI_FEC_INICIO BETWEEN @FECHAINICIO AND @FECHAFINAL
)WF
JOIN
(
SELECT SOL_CON_SOLICITUD
,SOL_ID_SOLICITUD
FROM OP_SOLICITUDES_TEMP
where SOL_ID_PROMOTORIA = @ID_PROMO
)SOL
ON
WF.WBI_ID_SOLICITUD = SOL.SOL_ID_SOLICITUD
)

2) The way that I would make this query

SELECT SOL.SOL_CON_SOLICITUD
,SOL.SOL_ID_SOLICITUD , WF.WBI_NOMBRE_PROCESO
,WF.WBI_ESTATUS
,cast(WF.WBI_ID_SOLICITUD as bigint)WBI_ID_SOLICITUD
,WF.WBI_FEC_INICIO
FROM WF_BITACORAS WF inner join OP_SOLICITUDES_TEMP SOL on
WF.WBI_CVE_PROMOTORIA=SOL.SOL_ID_PROMOTORIA
and WF.WBI_ID_SOLICITUD = SOL.SOL_ID_SOLICITUD
WHERE WF.WBI_CVE_PROMOTORIA = @ID_PROMO
AND WF.WBI_NOMBRE_PROCESO IN ('Servicios_V2','LotesValijas')
AND WF.WBI_ESTATUS = @ESTATUS
AND WF.WBI_FEC_INICIO BETWEEN @FECHAINICIO AND @FECHAFINAL

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-03-10 : 14:04:46

Which query is better is a relative question.. Meaning relative to how much data is being referenced (how many records) and presumably relative to how long it takes to execute a query. These things have to be determined (or analyzed) by you.

If you are simply concerned with which one runs faster than the answer is simple. Run them both under various conditions and see for yourself.
You can setup a trace and monitor with the profiler to see how they perform under certain load conditions. It may also be important to you when the queries are run (like when the server is most active) or it may simply be a matter of how big the recordset is.

These are not things the forum would know. You would have to define them yourself.

If both queries perform about the same then it is simply a matter of preference or which one is easier to interpret.

r&r

shifis
Posting Yak Master

157 Posts

Posted - 2009-03-10 : 15:18:09

Thanks

My question is because I will never do the query in the way they did (Query 1).
For me it is more confusing and I have never seem something like that, and I want to know if it is a good practice.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 03:15:51

Query can be more effective due to the WHERE clauses.
But as said before, start a trace in SQL Profiler and run both queries after another.

It's all about knowing your data.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources