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.
| Author |
Topic |
|
chechu
Starting Member
9 Posts |
 Posted - 2011-04-13 : 11:04:54
|
| Hi there,I need to optimize the following SQL transaction but I do not really know what to change to improve its performance, any ideas??As far as i can see, the select is quite good optimized, the problem is at "WHERE" clauses.SELECT top 5 tbRes.Id_Res, tbRes.Res_Localizador, tbLres.lre_horavuelovuelta, tbLres.lre_aeropuertodestino,tbLres.lre_horavueloida, tbResSerGen.RSG_FechaIni as fechaSalida, tblres.lre_vueloregreso, tbResSerGen.RSG_FechaFin, isnull(convert(varchar,tbAsigSer.ASe_Fecha,108),tbResSerGen.rsg_horainicio) as SalidaHotel, tbResSerGen.id_predst, tbResSerGen.id_preori, isnull(tbPuntRec.Pre_Nombre,tbResSerGen.RSG_preDireccionOri) as puntRecogida,(select top 1 z.zon_nombre_es from tbl_cachezona cz inner join tbl_zona z on cz.Zon_Padre = z.Id_Zon where cz.Zon_Nivel = 0 and cz.Zon_Hijo = tbResSerGen.rsg_origen) Origen,(select top 1 z.zon_nombre_es from tbl_cachezona cz inner join tbl_zona z on cz.Zon_padre = z.Id_Zon where cz.Zon_Nivel = 1 and cz.Zon_hijo = (select top 1 Id_Zon from tbl_Zona where zon_codigo = tbLres.lre_aeropuertodestino )) DestinoFROM Tbl_Reserva as tbRes INNER JOIN Tbl_LineaReserva as tbLres ON tbRes.Id_Res = tbLres.Id_Res INNER JOIN Tbl_ReservaServicioGenerico as tbResSerGen ON tbRes.Id_Res = tbResSerGen.Id_Res AND tbLres.Id_LRe = tbResSerGen.Id_LRe left JOIN Tbl_AsignacionServicio as tbAsigSer ON tbResSerGen.Id_LRe = tbAsigSer.Id_LRe Left JOIN Tbl_PuntoRecogida as tbPuntRec ON tbResSerGen.id_preori = tbPuntRec.Id_Pre inner join Tbl_TipoServicio tsr on tsr.Id_TSr = tbResSerGen.id_tsrinner join Tbl_Zona as tbZnOri on tbZnOri.Id_Zon = tbResSerGen.rsg_origeninner join Tbl_Zona as tbZnDst on tbZnDst.Id_Zon = tbResSerGen.rsg_destinowhere ( tsr.tsr_intern LIKE '%Tranf%' or tsr.TSr_Padre in ( SELECT id_tsr from tbl_TipoServicio WHERE tsr_intern like '%Tranf%'))and 'OUT' = CASE WHEN (upper(isnull(tbResSerGen.RSG_tipozonaOrigen,tbZnOri.Zon_Tipo)) IN ('ARP','PRT','EST')) THEN 'IN' WHEN (upper(isnull(tbResSerGen.RSG_tipozonaDestino,tbZnDst.Zon_Tipo)) IN ('ARP','PRT','EST')) THEN 'OUT' ELSE 'EE' END order by 1 descWith the data returned a data table is created, but the transact is taking too much time.Thanks in advancechechu. |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-04-13 : 21:53:46
|
| >> I need to optimize the following SQL transaction but I do not really know what to change to improve its performance, any ideas? <<Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. Bad DDL leads to bad queries. When you see a table name prefixed with “tb_” or “tbl_” , I am pretty sure that this is bad DDL. We don't use TOP, we don't use CONVERT. SQL programers use COALESCE and not ISNULL(). Your CHECK() constraints should assure that you have upper case in the table. Data elements do not change names from table to table (CZ.zon_padre = Z.zon_id is wrong and “id” goes on the end as an attributive property in ISO-11179). Putting scalar table expression in a SELECT list is insanely expensive and a bitch to optimize. Without DDL and specs, we cannot help you. But my guess is that if you fixed the schema, you would gain 2-3 orders of magnitude performance improvement. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
 |
|
|
|
|
|
|
|
Optimize following SQL transact