Hi
I am looking to migrate mysql to SQL Server any good tool to do that
My DTS wizard doesn't give the option of mysql
Thank you,
SamuelI've used DTS against mysql - it's doable. Make sure you
have installed the drivers on your PC and the SQL Server
box. You can download mysql drivers from:
http://dev.mysql.com/downloads/
-Sue
On Mon, 22 May 2006 21:51:18 +0100, "Samuel Shulman"
<samuel.shulman@.ntlworld.com> wrote:
>Hi
>I am looking to migrate mysql to SQL Server any good tool to do that
>My DTS wizard doesn't give the option of mysql
>Thank you,
>Samuel
>|||I saw both your replies, thank you just what I needed
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:e3yQ5FefGHA.2416@.TK2MSFTNGP03.phx.gbl...
> Hi
> I am looking to migrate mysql to SQL Server any good tool to do that
> My DTS wizard doesn't give the option of mysql
> Thank you,
> Samuel
>
>
Showing posts with label hii. Show all posts
Showing posts with label hii. Show all posts
Monday, March 12, 2012
MySQL OLEDB Driver
Hi!
I have a small question, there are OLEDB drivers availible for .NET on the MYSQL download site. But when I install them I'm not able to see them inside SQLIS, I've tried them inside Visual Studio and they work fine.
Is there a way to get them "registred" or add them as a reference inside SQLIS so that I may choose them when setting up an OLEDB Source / Destination?For performance reasons, the Integration Services Service caches (as DTS used to do) the list of available components and, among other things, OLE DB Providers, so that the designer doesn't need to waste time gathering those lists from the registry and the file system. I'm just speculating here, but I'm guessing that you might need to do something to refresh that cached list, such as stop and re-start the Service (or reboot). A little-known dialog box in DTS times has a "Refresh Cache" button, but I haven't seen an equivalent button in SSIS.
-Doug
|||Don't think that's the problem, I think the problem is that the driver doesn't get registered in the windows registery. Is there a way to add some keys to the windows registery to manually register the .dll files as OLEDB drivers?|||Hi, have you figured out that one?|||You say OLEDB drivers for .NET but that doesn't make sense to me. OLEDB is not a .NET provider instead it would be ADO.NET. Are the drivers OLEDB or are they .NET. If they are .NET then they wouldn't be available from our OLEDB source but from our DataReaderSource, which uses .NET connections. If they are indeed OLEDB then if they are not showing up then they are not registering themselves correctly. You could try regsvr32 to reregister them but you might have to contact MySQL support and ask them for help.
Thanks,
Matt
MySQL OLEDB Driver
Hi!
I have a small question, there are OLEDB drivers availible for .NET on the MYSQL download site. But when I install them I'm not able to see them inside SQLIS, I've tried them inside Visual Studio and they work fine.
Is there a way to get them "registred" or add them as a reference inside SQLIS so that I may choose them when setting up an OLEDB Source / Destination?For performance reasons, the Integration Services Service caches (as DTS used to do) the list of available components and, among other things, OLE DB Providers, so that the designer doesn't need to waste time gathering those lists from the registry and the file system. I'm just speculating here, but I'm guessing that you might need to do something to refresh that cached list, such as stop and re-start the Service (or reboot). A little-known dialog box in DTS times has a "Refresh Cache" button, but I haven't seen an equivalent button in SSIS.
-Doug
|||Don't think that's the problem, I think the problem is that the driver doesn't get registered in the windows registery. Is there a way to add some keys to the windows registery to manually register the .dll files as OLEDB drivers?|||Hi, have you figured out that one?|||You say OLEDB drivers for .NET but that doesn't make sense to me. OLEDB is not a .NET provider instead it would be ADO.NET. Are the drivers OLEDB or are they .NET. If they are .NET then they wouldn't be available from our OLEDB source but from our DataReaderSource, which uses .NET connections. If they are indeed OLEDB then if they are not showing up then they are not registering themselves correctly. You could try regsvr32 to reregister them but you might have to contact MySQL support and ask them for help.
Thanks,
Matt
Wednesday, March 7, 2012
My Transaction Log size very strange
Hi
I use SQL Server 2000
I use Full recuperation mode.
My DB size is 458 MB (344 in use, 114 available)
My log size is 18 MB
I do Full backup every hour starting at 8:00 am until 21:00
I do transaction backup every 10 minutes.
The problem is that the size of the transaction log backup is strange, as
follows:
Time Size(KB) filename
....
05:52 78.336 myDB_tlog_200507140552.TRN
06:02 78.336 myDB_tlog_200507140602.TRN
06:12 78.336 myDB_tlog_200507140612.TRN
06:22 78.336 myDB_tlog_200507140622.TRN
06:32 11.776 myDB_tlog_200507140632.TRN
06:42 11.776 myDB_tlog_200507140642.TRN
06:52 78.336 myDB_tlog_200507140652.TRN
07:03 359.117.312 myDB_tlog_200507140703.TRN Note
07:12 133.072.384 myDB_tlog_200507140712.TRN Note
07:22 291.328 myDB_tlog_200507140722.TRN
07:32 291.328 myDB_tlog_200507140732.TRN
07:42 291.328 myDB_tlog_200507140742.TRN
07:52 356.864 myDB_tlog_200507140752.TRN
08:02 356.864 myDB_tlog_200507140802.TRN
08:12 356.864 myDB_tlog_200507140812.TRN
08:22 357.888 myDB_tlog_200507140822.TRN
08:32 422.400 myDB_tlog_200507140832.TRN
08:42 10.663.424 myDB_tlog_200507140842.TRN Note
08:52 437.760 myDB_tlog_200507140852.TRN
09:02 437.760 myDB_tlog_200507140902.TRN
09:12 699.904 myDB_tlog_200507140912.TRN
09:22 503.296 myDB_tlog_200507140922.TRN
09:32 568.832 myDB_tlog_200507140932.TRN
09:42 568.832 myDB_tlog_200507140942.TRN
09:52 569.856 myDB_tlog_200507140952.TRN
10:02 372.224 myDB_tlog_200507141002.TRN
10:12 10.442.240 myDB_tlog_200507141012.TRN Note
10:22 413.184 myDB_tlog_200507141022.TRN
10:32 545.280 myDB_tlog_200507141032.TRN
10:42 479.744 myDB_tlog_200507141042.TRN
10:52 414.208 myDB_tlog_200507141052.TRN
11:02 479.744 myDB_tlog_200507141102.TRN
11:12 413.184 myDB_tlog_200507141112.TRN
11:22 414.208 myDB_tlog_200507141122.TRN
11:32 546.304 myDB_tlog_200507141132.TRN
11:42 1.093.120 myDB_tlog_200507141142.TRN
11:52 303.616 myDB_tlog_200507141152.TRN
12:02 436.736 myDB_tlog_200507141202.TRN
12:12 369.152 myDB_tlog_200507141212.TRN
12:22 239.104 myDB_tlog_200507141222.TRN
12:32 172.544 myDB_tlog_200507141232.TRN
12:42 107.008 myDB_tlog_200507141242.TRN
12:52 172.544 myDB_tlog_200507141252.TRN
13:02 239.104 myDB_tlog_200507141302.TRN
13:12 9.269.760 myDB_tlog_200507141312.TRN Note
13:22 158.208 myDB_tlog_200507141322.TRN
13:32 158.208 myDB_tlog_200507141332.TRN
13:42 92.672 myDB_tlog_200507141342.TRN
13:52 158.208 myDB_tlog_200507141352.TRN
14:02 92.672 myDB_tlog_200507141402.TRN
14:12 92.672 myDB_tlog_200507141412.TRN
14:22 92.672 myDB_tlog_200507141422.TRN
14:32 289.280 myDB_tlog_200507141432.TRN
14:42 16.926.208 myDB_tlog_200507141442.TRN Note
14:52 280.064 myDB_tlog_200507141452.TRN
15:02 411.136 myDB_tlog_200507141502.TRN
Any ideas?
I want to know what is happening, and how to fix it.
Thanks in advance.
Hi,
The Size of the transaction log backup will be bigger if you have Bulk
transaction or if you are doing a maintanence operation such
as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
Looking in to the current size the transaction log (LDF) I feel that you are
in safe side.
Thanks
Hari
SQL Server MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi
> I use SQL Server 2000
> I use Full recuperation mode.
> My DB size is 458 MB (344 in use, 114 available)
> My log size is 18 MB
> I do Full backup every hour starting at 8:00 am until 21:00
> I do transaction backup every 10 minutes.
> The problem is that the size of the transaction log backup is strange, as
> follows:
> Time Size(KB) filename
> ...
> 05:52 78.336 myDB_tlog_200507140552.TRN
> 06:02 78.336 myDB_tlog_200507140602.TRN
> 06:12 78.336 myDB_tlog_200507140612.TRN
> 06:22 78.336 myDB_tlog_200507140622.TRN
> 06:32 11.776 myDB_tlog_200507140632.TRN
> 06:42 11.776 myDB_tlog_200507140642.TRN
> 06:52 78.336 myDB_tlog_200507140652.TRN
> 07:03 359.117.312 myDB_tlog_200507140703.TRN Note
> 07:12 133.072.384 myDB_tlog_200507140712.TRN Note
> 07:22 291.328 myDB_tlog_200507140722.TRN
> 07:32 291.328 myDB_tlog_200507140732.TRN
> 07:42 291.328 myDB_tlog_200507140742.TRN
> 07:52 356.864 myDB_tlog_200507140752.TRN
> 08:02 356.864 myDB_tlog_200507140802.TRN
> 08:12 356.864 myDB_tlog_200507140812.TRN
> 08:22 357.888 myDB_tlog_200507140822.TRN
> 08:32 422.400 myDB_tlog_200507140832.TRN
> 08:42 10.663.424 myDB_tlog_200507140842.TRN Note
> 08:52 437.760 myDB_tlog_200507140852.TRN
> 09:02 437.760 myDB_tlog_200507140902.TRN
> 09:12 699.904 myDB_tlog_200507140912.TRN
> 09:22 503.296 myDB_tlog_200507140922.TRN
> 09:32 568.832 myDB_tlog_200507140932.TRN
> 09:42 568.832 myDB_tlog_200507140942.TRN
> 09:52 569.856 myDB_tlog_200507140952.TRN
> 10:02 372.224 myDB_tlog_200507141002.TRN
> 10:12 10.442.240 myDB_tlog_200507141012.TRN Note
> 10:22 413.184 myDB_tlog_200507141022.TRN
> 10:32 545.280 myDB_tlog_200507141032.TRN
> 10:42 479.744 myDB_tlog_200507141042.TRN
> 10:52 414.208 myDB_tlog_200507141052.TRN
> 11:02 479.744 myDB_tlog_200507141102.TRN
> 11:12 413.184 myDB_tlog_200507141112.TRN
> 11:22 414.208 myDB_tlog_200507141122.TRN
> 11:32 546.304 myDB_tlog_200507141132.TRN
> 11:42 1.093.120 myDB_tlog_200507141142.TRN
> 11:52 303.616 myDB_tlog_200507141152.TRN
> 12:02 436.736 myDB_tlog_200507141202.TRN
> 12:12 369.152 myDB_tlog_200507141212.TRN
> 12:22 239.104 myDB_tlog_200507141222.TRN
> 12:32 172.544 myDB_tlog_200507141232.TRN
> 12:42 107.008 myDB_tlog_200507141242.TRN
> 12:52 172.544 myDB_tlog_200507141252.TRN
> 13:02 239.104 myDB_tlog_200507141302.TRN
> 13:12 9.269.760 myDB_tlog_200507141312.TRN Note
> 13:22 158.208 myDB_tlog_200507141322.TRN
> 13:32 158.208 myDB_tlog_200507141332.TRN
> 13:42 92.672 myDB_tlog_200507141342.TRN
> 13:52 158.208 myDB_tlog_200507141352.TRN
> 14:02 92.672 myDB_tlog_200507141402.TRN
> 14:12 92.672 myDB_tlog_200507141412.TRN
> 14:22 92.672 myDB_tlog_200507141422.TRN
> 14:32 289.280 myDB_tlog_200507141432.TRN
> 14:42 16.926.208 myDB_tlog_200507141442.TRN Note
> 14:52 280.064 myDB_tlog_200507141452.TRN
> 15:02 411.136 myDB_tlog_200507141502.TRN
> Any ideas?
> I want to know what is happening, and how to fix it.
> Thanks in advance.
>
>
|||Thanks Hari.
But I'm still confused about then changing size!!!
From 300 KB to 16 MB.
Any ideas?
Thanks in advance.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> The Size of the transaction log backup will be bigger if you have Bulk
> transaction or if you are doing a maintanence operation such
> as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
> Looking in to the current size the transaction log (LDF) I feel that you
are[vbcol=seagreen]
> in safe side.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
> news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
as
>
|||The amount of data in the log is totally dependant on what you are doing
since the last log backup. Use profiler to see what is happening during the
time when the log backups are large and you will see what i causing the
difference.
Andrew J. Kelly SQL MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:%23pEhNpLiFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Thanks Hari.
> But I'm still confused about then changing size!!!
> From 300 KB to 16 MB.
> Any ideas?
> Thanks in advance.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
> news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> are
> as
>
I use SQL Server 2000
I use Full recuperation mode.
My DB size is 458 MB (344 in use, 114 available)
My log size is 18 MB
I do Full backup every hour starting at 8:00 am until 21:00
I do transaction backup every 10 minutes.
The problem is that the size of the transaction log backup is strange, as
follows:
Time Size(KB) filename
....
05:52 78.336 myDB_tlog_200507140552.TRN
06:02 78.336 myDB_tlog_200507140602.TRN
06:12 78.336 myDB_tlog_200507140612.TRN
06:22 78.336 myDB_tlog_200507140622.TRN
06:32 11.776 myDB_tlog_200507140632.TRN
06:42 11.776 myDB_tlog_200507140642.TRN
06:52 78.336 myDB_tlog_200507140652.TRN
07:03 359.117.312 myDB_tlog_200507140703.TRN Note
07:12 133.072.384 myDB_tlog_200507140712.TRN Note
07:22 291.328 myDB_tlog_200507140722.TRN
07:32 291.328 myDB_tlog_200507140732.TRN
07:42 291.328 myDB_tlog_200507140742.TRN
07:52 356.864 myDB_tlog_200507140752.TRN
08:02 356.864 myDB_tlog_200507140802.TRN
08:12 356.864 myDB_tlog_200507140812.TRN
08:22 357.888 myDB_tlog_200507140822.TRN
08:32 422.400 myDB_tlog_200507140832.TRN
08:42 10.663.424 myDB_tlog_200507140842.TRN Note
08:52 437.760 myDB_tlog_200507140852.TRN
09:02 437.760 myDB_tlog_200507140902.TRN
09:12 699.904 myDB_tlog_200507140912.TRN
09:22 503.296 myDB_tlog_200507140922.TRN
09:32 568.832 myDB_tlog_200507140932.TRN
09:42 568.832 myDB_tlog_200507140942.TRN
09:52 569.856 myDB_tlog_200507140952.TRN
10:02 372.224 myDB_tlog_200507141002.TRN
10:12 10.442.240 myDB_tlog_200507141012.TRN Note
10:22 413.184 myDB_tlog_200507141022.TRN
10:32 545.280 myDB_tlog_200507141032.TRN
10:42 479.744 myDB_tlog_200507141042.TRN
10:52 414.208 myDB_tlog_200507141052.TRN
11:02 479.744 myDB_tlog_200507141102.TRN
11:12 413.184 myDB_tlog_200507141112.TRN
11:22 414.208 myDB_tlog_200507141122.TRN
11:32 546.304 myDB_tlog_200507141132.TRN
11:42 1.093.120 myDB_tlog_200507141142.TRN
11:52 303.616 myDB_tlog_200507141152.TRN
12:02 436.736 myDB_tlog_200507141202.TRN
12:12 369.152 myDB_tlog_200507141212.TRN
12:22 239.104 myDB_tlog_200507141222.TRN
12:32 172.544 myDB_tlog_200507141232.TRN
12:42 107.008 myDB_tlog_200507141242.TRN
12:52 172.544 myDB_tlog_200507141252.TRN
13:02 239.104 myDB_tlog_200507141302.TRN
13:12 9.269.760 myDB_tlog_200507141312.TRN Note
13:22 158.208 myDB_tlog_200507141322.TRN
13:32 158.208 myDB_tlog_200507141332.TRN
13:42 92.672 myDB_tlog_200507141342.TRN
13:52 158.208 myDB_tlog_200507141352.TRN
14:02 92.672 myDB_tlog_200507141402.TRN
14:12 92.672 myDB_tlog_200507141412.TRN
14:22 92.672 myDB_tlog_200507141422.TRN
14:32 289.280 myDB_tlog_200507141432.TRN
14:42 16.926.208 myDB_tlog_200507141442.TRN Note
14:52 280.064 myDB_tlog_200507141452.TRN
15:02 411.136 myDB_tlog_200507141502.TRN
Any ideas?
I want to know what is happening, and how to fix it.
Thanks in advance.
Hi,
The Size of the transaction log backup will be bigger if you have Bulk
transaction or if you are doing a maintanence operation such
as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
Looking in to the current size the transaction log (LDF) I feel that you are
in safe side.
Thanks
Hari
SQL Server MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi
> I use SQL Server 2000
> I use Full recuperation mode.
> My DB size is 458 MB (344 in use, 114 available)
> My log size is 18 MB
> I do Full backup every hour starting at 8:00 am until 21:00
> I do transaction backup every 10 minutes.
> The problem is that the size of the transaction log backup is strange, as
> follows:
> Time Size(KB) filename
> ...
> 05:52 78.336 myDB_tlog_200507140552.TRN
> 06:02 78.336 myDB_tlog_200507140602.TRN
> 06:12 78.336 myDB_tlog_200507140612.TRN
> 06:22 78.336 myDB_tlog_200507140622.TRN
> 06:32 11.776 myDB_tlog_200507140632.TRN
> 06:42 11.776 myDB_tlog_200507140642.TRN
> 06:52 78.336 myDB_tlog_200507140652.TRN
> 07:03 359.117.312 myDB_tlog_200507140703.TRN Note
> 07:12 133.072.384 myDB_tlog_200507140712.TRN Note
> 07:22 291.328 myDB_tlog_200507140722.TRN
> 07:32 291.328 myDB_tlog_200507140732.TRN
> 07:42 291.328 myDB_tlog_200507140742.TRN
> 07:52 356.864 myDB_tlog_200507140752.TRN
> 08:02 356.864 myDB_tlog_200507140802.TRN
> 08:12 356.864 myDB_tlog_200507140812.TRN
> 08:22 357.888 myDB_tlog_200507140822.TRN
> 08:32 422.400 myDB_tlog_200507140832.TRN
> 08:42 10.663.424 myDB_tlog_200507140842.TRN Note
> 08:52 437.760 myDB_tlog_200507140852.TRN
> 09:02 437.760 myDB_tlog_200507140902.TRN
> 09:12 699.904 myDB_tlog_200507140912.TRN
> 09:22 503.296 myDB_tlog_200507140922.TRN
> 09:32 568.832 myDB_tlog_200507140932.TRN
> 09:42 568.832 myDB_tlog_200507140942.TRN
> 09:52 569.856 myDB_tlog_200507140952.TRN
> 10:02 372.224 myDB_tlog_200507141002.TRN
> 10:12 10.442.240 myDB_tlog_200507141012.TRN Note
> 10:22 413.184 myDB_tlog_200507141022.TRN
> 10:32 545.280 myDB_tlog_200507141032.TRN
> 10:42 479.744 myDB_tlog_200507141042.TRN
> 10:52 414.208 myDB_tlog_200507141052.TRN
> 11:02 479.744 myDB_tlog_200507141102.TRN
> 11:12 413.184 myDB_tlog_200507141112.TRN
> 11:22 414.208 myDB_tlog_200507141122.TRN
> 11:32 546.304 myDB_tlog_200507141132.TRN
> 11:42 1.093.120 myDB_tlog_200507141142.TRN
> 11:52 303.616 myDB_tlog_200507141152.TRN
> 12:02 436.736 myDB_tlog_200507141202.TRN
> 12:12 369.152 myDB_tlog_200507141212.TRN
> 12:22 239.104 myDB_tlog_200507141222.TRN
> 12:32 172.544 myDB_tlog_200507141232.TRN
> 12:42 107.008 myDB_tlog_200507141242.TRN
> 12:52 172.544 myDB_tlog_200507141252.TRN
> 13:02 239.104 myDB_tlog_200507141302.TRN
> 13:12 9.269.760 myDB_tlog_200507141312.TRN Note
> 13:22 158.208 myDB_tlog_200507141322.TRN
> 13:32 158.208 myDB_tlog_200507141332.TRN
> 13:42 92.672 myDB_tlog_200507141342.TRN
> 13:52 158.208 myDB_tlog_200507141352.TRN
> 14:02 92.672 myDB_tlog_200507141402.TRN
> 14:12 92.672 myDB_tlog_200507141412.TRN
> 14:22 92.672 myDB_tlog_200507141422.TRN
> 14:32 289.280 myDB_tlog_200507141432.TRN
> 14:42 16.926.208 myDB_tlog_200507141442.TRN Note
> 14:52 280.064 myDB_tlog_200507141452.TRN
> 15:02 411.136 myDB_tlog_200507141502.TRN
> Any ideas?
> I want to know what is happening, and how to fix it.
> Thanks in advance.
>
>
|||Thanks Hari.
But I'm still confused about then changing size!!!
From 300 KB to 16 MB.
Any ideas?
Thanks in advance.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> The Size of the transaction log backup will be bigger if you have Bulk
> transaction or if you are doing a maintanence operation such
> as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
> Looking in to the current size the transaction log (LDF) I feel that you
are[vbcol=seagreen]
> in safe side.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
> news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
as
>
|||The amount of data in the log is totally dependant on what you are doing
since the last log backup. Use profiler to see what is happening during the
time when the log backups are large and you will see what i causing the
difference.
Andrew J. Kelly SQL MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:%23pEhNpLiFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Thanks Hari.
> But I'm still confused about then changing size!!!
> From 300 KB to 16 MB.
> Any ideas?
> Thanks in advance.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
> news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> are
> as
>
My Transaction Log size very strange
Hi
I use SQL Server 2000
I use Full recuperation mode.
My DB size is 458 MB (344 in use, 114 available)
My log size is 18 MB
I do Full backup every hour starting at 8:00 am until 21:00
I do transaction backup every 10 minutes.
The problem is that the size of the transaction log backup is strange, as
follows:
Time Size(KB) filename
...
05:52 78.336 myDB_tlog_200507140552.TRN
06:02 78.336 myDB_tlog_200507140602.TRN
06:12 78.336 myDB_tlog_200507140612.TRN
06:22 78.336 myDB_tlog_200507140622.TRN
06:32 11.776 myDB_tlog_200507140632.TRN
06:42 11.776 myDB_tlog_200507140642.TRN
06:52 78.336 myDB_tlog_200507140652.TRN
07:03 359.117.312 myDB_tlog_200507140703.TRN Note
07:12 133.072.384 myDB_tlog_200507140712.TRN Note
07:22 291.328 myDB_tlog_200507140722.TRN
07:32 291.328 myDB_tlog_200507140732.TRN
07:42 291.328 myDB_tlog_200507140742.TRN
07:52 356.864 myDB_tlog_200507140752.TRN
08:02 356.864 myDB_tlog_200507140802.TRN
08:12 356.864 myDB_tlog_200507140812.TRN
08:22 357.888 myDB_tlog_200507140822.TRN
08:32 422.400 myDB_tlog_200507140832.TRN
08:42 10.663.424 myDB_tlog_200507140842.TRN Note
08:52 437.760 myDB_tlog_200507140852.TRN
09:02 437.760 myDB_tlog_200507140902.TRN
09:12 699.904 myDB_tlog_200507140912.TRN
09:22 503.296 myDB_tlog_200507140922.TRN
09:32 568.832 myDB_tlog_200507140932.TRN
09:42 568.832 myDB_tlog_200507140942.TRN
09:52 569.856 myDB_tlog_200507140952.TRN
10:02 372.224 myDB_tlog_200507141002.TRN
10:12 10.442.240 myDB_tlog_200507141012.TRN Note
10:22 413.184 myDB_tlog_200507141022.TRN
10:32 545.280 myDB_tlog_200507141032.TRN
10:42 479.744 myDB_tlog_200507141042.TRN
10:52 414.208 myDB_tlog_200507141052.TRN
11:02 479.744 myDB_tlog_200507141102.TRN
11:12 413.184 myDB_tlog_200507141112.TRN
11:22 414.208 myDB_tlog_200507141122.TRN
11:32 546.304 myDB_tlog_200507141132.TRN
11:42 1.093.120 myDB_tlog_200507141142.TRN
11:52 303.616 myDB_tlog_200507141152.TRN
12:02 436.736 myDB_tlog_200507141202.TRN
12:12 369.152 myDB_tlog_200507141212.TRN
12:22 239.104 myDB_tlog_200507141222.TRN
12:32 172.544 myDB_tlog_200507141232.TRN
12:42 107.008 myDB_tlog_200507141242.TRN
12:52 172.544 myDB_tlog_200507141252.TRN
13:02 239.104 myDB_tlog_200507141302.TRN
13:12 9.269.760 myDB_tlog_200507141312.TRN Note
13:22 158.208 myDB_tlog_200507141322.TRN
13:32 158.208 myDB_tlog_200507141332.TRN
13:42 92.672 myDB_tlog_200507141342.TRN
13:52 158.208 myDB_tlog_200507141352.TRN
14:02 92.672 myDB_tlog_200507141402.TRN
14:12 92.672 myDB_tlog_200507141412.TRN
14:22 92.672 myDB_tlog_200507141422.TRN
14:32 289.280 myDB_tlog_200507141432.TRN
14:42 16.926.208 myDB_tlog_200507141442.TRN Note
14:52 280.064 myDB_tlog_200507141452.TRN
15:02 411.136 myDB_tlog_200507141502.TRN
Any ideas?
I want to know what is happening, and how to fix it.
Thanks in advance.Hi,
The Size of the transaction log backup will be bigger if you have Bulk
transaction or if you are doing a maintanence operation such
as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
Looking in to the current size the transaction log (LDF) I feel that you are
in safe side.
Thanks
Hari
SQL Server MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi
> I use SQL Server 2000
> I use Full recuperation mode.
> My DB size is 458 MB (344 in use, 114 available)
> My log size is 18 MB
> I do Full backup every hour starting at 8:00 am until 21:00
> I do transaction backup every 10 minutes.
> The problem is that the size of the transaction log backup is strange, as
> follows:
> Time Size(KB) filename
> ...
> 05:52 78.336 myDB_tlog_200507140552.TRN
> 06:02 78.336 myDB_tlog_200507140602.TRN
> 06:12 78.336 myDB_tlog_200507140612.TRN
> 06:22 78.336 myDB_tlog_200507140622.TRN
> 06:32 11.776 myDB_tlog_200507140632.TRN
> 06:42 11.776 myDB_tlog_200507140642.TRN
> 06:52 78.336 myDB_tlog_200507140652.TRN
> 07:03 359.117.312 myDB_tlog_200507140703.TRN Note
> 07:12 133.072.384 myDB_tlog_200507140712.TRN Note
> 07:22 291.328 myDB_tlog_200507140722.TRN
> 07:32 291.328 myDB_tlog_200507140732.TRN
> 07:42 291.328 myDB_tlog_200507140742.TRN
> 07:52 356.864 myDB_tlog_200507140752.TRN
> 08:02 356.864 myDB_tlog_200507140802.TRN
> 08:12 356.864 myDB_tlog_200507140812.TRN
> 08:22 357.888 myDB_tlog_200507140822.TRN
> 08:32 422.400 myDB_tlog_200507140832.TRN
> 08:42 10.663.424 myDB_tlog_200507140842.TRN Note
> 08:52 437.760 myDB_tlog_200507140852.TRN
> 09:02 437.760 myDB_tlog_200507140902.TRN
> 09:12 699.904 myDB_tlog_200507140912.TRN
> 09:22 503.296 myDB_tlog_200507140922.TRN
> 09:32 568.832 myDB_tlog_200507140932.TRN
> 09:42 568.832 myDB_tlog_200507140942.TRN
> 09:52 569.856 myDB_tlog_200507140952.TRN
> 10:02 372.224 myDB_tlog_200507141002.TRN
> 10:12 10.442.240 myDB_tlog_200507141012.TRN Note
> 10:22 413.184 myDB_tlog_200507141022.TRN
> 10:32 545.280 myDB_tlog_200507141032.TRN
> 10:42 479.744 myDB_tlog_200507141042.TRN
> 10:52 414.208 myDB_tlog_200507141052.TRN
> 11:02 479.744 myDB_tlog_200507141102.TRN
> 11:12 413.184 myDB_tlog_200507141112.TRN
> 11:22 414.208 myDB_tlog_200507141122.TRN
> 11:32 546.304 myDB_tlog_200507141132.TRN
> 11:42 1.093.120 myDB_tlog_200507141142.TRN
> 11:52 303.616 myDB_tlog_200507141152.TRN
> 12:02 436.736 myDB_tlog_200507141202.TRN
> 12:12 369.152 myDB_tlog_200507141212.TRN
> 12:22 239.104 myDB_tlog_200507141222.TRN
> 12:32 172.544 myDB_tlog_200507141232.TRN
> 12:42 107.008 myDB_tlog_200507141242.TRN
> 12:52 172.544 myDB_tlog_200507141252.TRN
> 13:02 239.104 myDB_tlog_200507141302.TRN
> 13:12 9.269.760 myDB_tlog_200507141312.TRN Note
> 13:22 158.208 myDB_tlog_200507141322.TRN
> 13:32 158.208 myDB_tlog_200507141332.TRN
> 13:42 92.672 myDB_tlog_200507141342.TRN
> 13:52 158.208 myDB_tlog_200507141352.TRN
> 14:02 92.672 myDB_tlog_200507141402.TRN
> 14:12 92.672 myDB_tlog_200507141412.TRN
> 14:22 92.672 myDB_tlog_200507141422.TRN
> 14:32 289.280 myDB_tlog_200507141432.TRN
> 14:42 16.926.208 myDB_tlog_200507141442.TRN Note
> 14:52 280.064 myDB_tlog_200507141452.TRN
> 15:02 411.136 myDB_tlog_200507141502.TRN
> Any ideas?
> I want to know what is happening, and how to fix it.
> Thanks in advance.
>
>|||Thanks Hari.
But I'm still confused about then changing size!!!
From 300 KB to 16 MB.
Any ideas?
Thanks in advance.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> The Size of the transaction log backup will be bigger if you have Bulk
> transaction or if you are doing a maintanence operation such
> as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
> Looking in to the current size the transaction log (LDF) I feel that you
are
> in safe side.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
> news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
as[vbcol=seagreen]
>|||The amount of data in the log is totally dependant on what you are doing
since the last log backup. Use profiler to see what is happening during the
time when the log backups are large and you will see what i causing the
difference.
Andrew J. Kelly SQL MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:%23pEhNpLiFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Thanks Hari.
> But I'm still confused about then changing size!!!
> From 300 KB to 16 MB.
> Any ideas?
> Thanks in advance.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
> news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> are
> as
>
I use SQL Server 2000
I use Full recuperation mode.
My DB size is 458 MB (344 in use, 114 available)
My log size is 18 MB
I do Full backup every hour starting at 8:00 am until 21:00
I do transaction backup every 10 minutes.
The problem is that the size of the transaction log backup is strange, as
follows:
Time Size(KB) filename
...
05:52 78.336 myDB_tlog_200507140552.TRN
06:02 78.336 myDB_tlog_200507140602.TRN
06:12 78.336 myDB_tlog_200507140612.TRN
06:22 78.336 myDB_tlog_200507140622.TRN
06:32 11.776 myDB_tlog_200507140632.TRN
06:42 11.776 myDB_tlog_200507140642.TRN
06:52 78.336 myDB_tlog_200507140652.TRN
07:03 359.117.312 myDB_tlog_200507140703.TRN Note
07:12 133.072.384 myDB_tlog_200507140712.TRN Note
07:22 291.328 myDB_tlog_200507140722.TRN
07:32 291.328 myDB_tlog_200507140732.TRN
07:42 291.328 myDB_tlog_200507140742.TRN
07:52 356.864 myDB_tlog_200507140752.TRN
08:02 356.864 myDB_tlog_200507140802.TRN
08:12 356.864 myDB_tlog_200507140812.TRN
08:22 357.888 myDB_tlog_200507140822.TRN
08:32 422.400 myDB_tlog_200507140832.TRN
08:42 10.663.424 myDB_tlog_200507140842.TRN Note
08:52 437.760 myDB_tlog_200507140852.TRN
09:02 437.760 myDB_tlog_200507140902.TRN
09:12 699.904 myDB_tlog_200507140912.TRN
09:22 503.296 myDB_tlog_200507140922.TRN
09:32 568.832 myDB_tlog_200507140932.TRN
09:42 568.832 myDB_tlog_200507140942.TRN
09:52 569.856 myDB_tlog_200507140952.TRN
10:02 372.224 myDB_tlog_200507141002.TRN
10:12 10.442.240 myDB_tlog_200507141012.TRN Note
10:22 413.184 myDB_tlog_200507141022.TRN
10:32 545.280 myDB_tlog_200507141032.TRN
10:42 479.744 myDB_tlog_200507141042.TRN
10:52 414.208 myDB_tlog_200507141052.TRN
11:02 479.744 myDB_tlog_200507141102.TRN
11:12 413.184 myDB_tlog_200507141112.TRN
11:22 414.208 myDB_tlog_200507141122.TRN
11:32 546.304 myDB_tlog_200507141132.TRN
11:42 1.093.120 myDB_tlog_200507141142.TRN
11:52 303.616 myDB_tlog_200507141152.TRN
12:02 436.736 myDB_tlog_200507141202.TRN
12:12 369.152 myDB_tlog_200507141212.TRN
12:22 239.104 myDB_tlog_200507141222.TRN
12:32 172.544 myDB_tlog_200507141232.TRN
12:42 107.008 myDB_tlog_200507141242.TRN
12:52 172.544 myDB_tlog_200507141252.TRN
13:02 239.104 myDB_tlog_200507141302.TRN
13:12 9.269.760 myDB_tlog_200507141312.TRN Note
13:22 158.208 myDB_tlog_200507141322.TRN
13:32 158.208 myDB_tlog_200507141332.TRN
13:42 92.672 myDB_tlog_200507141342.TRN
13:52 158.208 myDB_tlog_200507141352.TRN
14:02 92.672 myDB_tlog_200507141402.TRN
14:12 92.672 myDB_tlog_200507141412.TRN
14:22 92.672 myDB_tlog_200507141422.TRN
14:32 289.280 myDB_tlog_200507141432.TRN
14:42 16.926.208 myDB_tlog_200507141442.TRN Note
14:52 280.064 myDB_tlog_200507141452.TRN
15:02 411.136 myDB_tlog_200507141502.TRN
Any ideas?
I want to know what is happening, and how to fix it.
Thanks in advance.Hi,
The Size of the transaction log backup will be bigger if you have Bulk
transaction or if you are doing a maintanence operation such
as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
Looking in to the current size the transaction log (LDF) I feel that you are
in safe side.
Thanks
Hari
SQL Server MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi
> I use SQL Server 2000
> I use Full recuperation mode.
> My DB size is 458 MB (344 in use, 114 available)
> My log size is 18 MB
> I do Full backup every hour starting at 8:00 am until 21:00
> I do transaction backup every 10 minutes.
> The problem is that the size of the transaction log backup is strange, as
> follows:
> Time Size(KB) filename
> ...
> 05:52 78.336 myDB_tlog_200507140552.TRN
> 06:02 78.336 myDB_tlog_200507140602.TRN
> 06:12 78.336 myDB_tlog_200507140612.TRN
> 06:22 78.336 myDB_tlog_200507140622.TRN
> 06:32 11.776 myDB_tlog_200507140632.TRN
> 06:42 11.776 myDB_tlog_200507140642.TRN
> 06:52 78.336 myDB_tlog_200507140652.TRN
> 07:03 359.117.312 myDB_tlog_200507140703.TRN Note
> 07:12 133.072.384 myDB_tlog_200507140712.TRN Note
> 07:22 291.328 myDB_tlog_200507140722.TRN
> 07:32 291.328 myDB_tlog_200507140732.TRN
> 07:42 291.328 myDB_tlog_200507140742.TRN
> 07:52 356.864 myDB_tlog_200507140752.TRN
> 08:02 356.864 myDB_tlog_200507140802.TRN
> 08:12 356.864 myDB_tlog_200507140812.TRN
> 08:22 357.888 myDB_tlog_200507140822.TRN
> 08:32 422.400 myDB_tlog_200507140832.TRN
> 08:42 10.663.424 myDB_tlog_200507140842.TRN Note
> 08:52 437.760 myDB_tlog_200507140852.TRN
> 09:02 437.760 myDB_tlog_200507140902.TRN
> 09:12 699.904 myDB_tlog_200507140912.TRN
> 09:22 503.296 myDB_tlog_200507140922.TRN
> 09:32 568.832 myDB_tlog_200507140932.TRN
> 09:42 568.832 myDB_tlog_200507140942.TRN
> 09:52 569.856 myDB_tlog_200507140952.TRN
> 10:02 372.224 myDB_tlog_200507141002.TRN
> 10:12 10.442.240 myDB_tlog_200507141012.TRN Note
> 10:22 413.184 myDB_tlog_200507141022.TRN
> 10:32 545.280 myDB_tlog_200507141032.TRN
> 10:42 479.744 myDB_tlog_200507141042.TRN
> 10:52 414.208 myDB_tlog_200507141052.TRN
> 11:02 479.744 myDB_tlog_200507141102.TRN
> 11:12 413.184 myDB_tlog_200507141112.TRN
> 11:22 414.208 myDB_tlog_200507141122.TRN
> 11:32 546.304 myDB_tlog_200507141132.TRN
> 11:42 1.093.120 myDB_tlog_200507141142.TRN
> 11:52 303.616 myDB_tlog_200507141152.TRN
> 12:02 436.736 myDB_tlog_200507141202.TRN
> 12:12 369.152 myDB_tlog_200507141212.TRN
> 12:22 239.104 myDB_tlog_200507141222.TRN
> 12:32 172.544 myDB_tlog_200507141232.TRN
> 12:42 107.008 myDB_tlog_200507141242.TRN
> 12:52 172.544 myDB_tlog_200507141252.TRN
> 13:02 239.104 myDB_tlog_200507141302.TRN
> 13:12 9.269.760 myDB_tlog_200507141312.TRN Note
> 13:22 158.208 myDB_tlog_200507141322.TRN
> 13:32 158.208 myDB_tlog_200507141332.TRN
> 13:42 92.672 myDB_tlog_200507141342.TRN
> 13:52 158.208 myDB_tlog_200507141352.TRN
> 14:02 92.672 myDB_tlog_200507141402.TRN
> 14:12 92.672 myDB_tlog_200507141412.TRN
> 14:22 92.672 myDB_tlog_200507141422.TRN
> 14:32 289.280 myDB_tlog_200507141432.TRN
> 14:42 16.926.208 myDB_tlog_200507141442.TRN Note
> 14:52 280.064 myDB_tlog_200507141452.TRN
> 15:02 411.136 myDB_tlog_200507141502.TRN
> Any ideas?
> I want to know what is happening, and how to fix it.
> Thanks in advance.
>
>|||Thanks Hari.
But I'm still confused about then changing size!!!
From 300 KB to 16 MB.
Any ideas?
Thanks in advance.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> The Size of the transaction log backup will be bigger if you have Bulk
> transaction or if you are doing a maintanence operation such
> as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
> Looking in to the current size the transaction log (LDF) I feel that you
are
> in safe side.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
> news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
as[vbcol=seagreen]
>|||The amount of data in the log is totally dependant on what you are doing
since the last log backup. Use profiler to see what is happening during the
time when the log backups are large and you will see what i causing the
difference.
Andrew J. Kelly SQL MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:%23pEhNpLiFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Thanks Hari.
> But I'm still confused about then changing size!!!
> From 300 KB to 16 MB.
> Any ideas?
> Thanks in advance.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
> news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> are
> as
>
my Store procedure spend TOO MANY TIME, why?
Hi
I have a sql that return about 35 rows
If I execute that sql in the analizer it spend 4 seconds (with
variables)
If I execute the same sql in a store procedure it spend 35 seconds
(with parameters)
The size of the variables or parameters are the same as the columns of the
tables so...
Why it can be?
This is the store procedure
CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
@.Campania int,
@.FechaDesde nchar(10),
@.FechaHasta nchar(10),
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2)
+ '-' + Left(@.FechaDesde, 2)
SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2)
+ '-' + Left(@.FechaHasta, 2)
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
-- DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <=
CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >=
CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
@.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
ORDER BY TGENERAL.PVP
END
RETURN @.@.ROWCOUNT
GORal,
First of all, you must change the name of your stored procedure.
Procedures named sp_* are treated differently by the query
processor. This name is used to indicate that there is a system
stored procedure in the master database available from all databases,
so the processor must first search the objects in master, find no
system procedure, then look for your procedure in your database.
If this doesn't solve the problem, consider adding WITH RECOMPILE
to the definition of the stored procedure. What you may be seeing is
the result of a cached plan for the stored procedure that is optimal
for one set of parameters and very bad for another. When you run
this query outside a procedure, the plan will not be cached, but
will be recompiled for the particular variable values.
If that still doesn't work, try declaring local variables for your
parameters and reassigning them inside the procedure:
...
declare @.Campania2 int,
@.FechaDesde2 nchar(10),
@.FechaHasta2 nchar(10),
@.Pais2 nchar(5),
@.Ciudad2 nchar(5),
@.Duracion2 int,
@.Precio2 int
select
@.Campania2 = @.Campania,
@.FechaDesde2 = @.FechaHasta,
..
This sometimes improves the choice of plan with or
without adding WITH RECOMPILE. In your case, it
might also be worthwhile to use local datetime variables for
the result of CONVERT(DATETIME, @.FechaHasta, 102),
CONVERT(DATETIME, @.FechaDesde, 102), and
CONVERT(DATETIME, GETDATE(), 102)
Queries this complicated with many parameters can be difficult
to optimize, particularly if the optimal query plan depends on
the particular parameter values.
Finally, I didn't look closely, but I don't quite understand the
purpose of
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
instead of just putting .Multimedias.IDtipoMultimedia = 0 into
the where clause before grouping. I'm also not sure why the
extra . before Multimedias.
Steve Kass
Drew University
Ral Martn wrote:
>Hi
> I have a sql that return about 35 rows
> If I execute that sql in the analizer it spend 4 seconds (with
>variables)
> If I execute the same sql in a store procedure it spend 35 seconds
>(with parameters)
>The size of the variables or parameters are the same as the columns of the
>tables so...
> Why it can be?
>This is the store procedure
>CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
> @.Campania int,
> @.FechaDesde nchar(10),
> @.FechaHasta nchar(10),
> @.Pais nchar(5),
> @.Ciudad nchar(5),
> @.Duracion int,
> @.Precio int
>AS
>
>DECLARE @.PrecioMin int, @.PrecioMax int
>SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2
)
>+ '-' + Left(@.FechaDesde, 2)
>SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2
)
>+ '-' + Left(@.FechaHasta, 2)
>DECLARE @.TablaAcrear varchar(2000)
>DECLARE @.STRINGINSERT varchar(4000)
>DECLARE @.STRINGadevolver varchar(1000)
>DECLARE @.NombreTablaTemporal nvarchar(250)
>BEGIN
>
> SET @.PrecioMin = 0
> SET @.PrecioMax = 999999
> IF @.Precio = 1
> BEGIN
> SET @.PrecioMin = 0
> SET @.PrecioMax = 300
> END
> ELSE IF @.Precio = 2
> BEGIN
> SET @.PrecioMin = 301
> SET @.PrecioMax = 600
> END
> ELSE IF @.Precio = 3
> BEGIN
> SET @.PrecioMin = 601
> SET @.PrecioMax = 900
> END
> ELSE IF @.Precio = 4
> BEGIN
> SET @.PrecioMin = 901
> SET @.PrecioMax = 1200
> END
> ELSE IF @.Precio = 5
> BEGIN
> SET @.PrecioMin = 1201
> SET @.PrecioMax = 999999
> END
>
> DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
>decimal(9,0),
> @.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
>@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
> DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
>@.IDduracion int
> DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
>
> set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
>int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
>decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
> exec (@.TablaAcrear)
>
> -- CIUDADES
>
> -- DECLARE Cursor1 CURSOR FOR
>
> SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
>PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
>TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
>TGENERAL.IDduracion as IDduracion,
> TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
>Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
>Multimedias.alto as alto
> FROM (
> SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
>TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
> MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
>AS Ciudad
> FROM (
> SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
>MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
> MIX.IDduracion, MIX.nombreIDPVSRC AS
>nombreIDPVSRC, MIX.nombre AS Ciudad
> FROM (
> SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
>PVSRC.IDvuelo,
> PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
>Ciudades.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
>INNER JOIN
> Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
> Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
> CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
> WHERE
> (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
>(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AN
D
>(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
>(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
>(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
>@.Campania)
> AND (Ciudades.IDCIUDAD = @.Ciudad)
> GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
> PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
> )
> MIX
> INNER JOIN
> (
> SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
>PreciosXFechasPVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <=
>CONVERT(DATETIME, @.FechaHasta, 102)) AND
> (PreciosXFechasPVSRC.fechaBaja >=
>CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
>@.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> )
> TABLAPVSRC
> ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
>TABLAPVSRC.nombre
> GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
>MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
> ORDER BY MIX.PVP
> )
> MIXG
> INNER JOIN
> (
> SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
>PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
> PVSRC.IDpaquete
> FROM PreciosXFechasPVSRC INNER JOIN
> PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
>102)) AND
> (PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
>102)) AND
> (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
>PVSRC.IDpaquete
> )
> TABLAPVP
> ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
> )
> TGENERAL
> INNER JOIN PVSRC
> ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
> INNER JOIN
> .Multimedias ON .PVSRC.IDmultimedia =
>.Multimedias.IDmultimedia
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
>TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
>Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> ORDER BY TGENERAL.PVP
>
> END
>
>RETURN @.@.ROWCOUNT
>GO
>
>|||Thanks a lot Steve but with those changes it is worse than before.
I modified de "sp_" , added WITH RECOMPILE, added local parameters and I
modified the query
at the end with
WHERE (.Multimedias.IDtipoMultimedia = 0)
in stead of
HAVING (.Multimedias.IDtipoMultimedia = 0)
but now it spend 80 seconds so if you have more ideas...
now the store procedure is:
CREATE PROCEDURE BuscardorOfertasmejorCIUDADES
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
DROP TABLE BORRAR4
SET @.NombreTablaTemporal = '_tempBusc' + LTRIM(RTRIM(@.Pais)) +
LTRIM(RTRIM(@.Ciudad)) + LTRIM(RTRIM(STR(@.Campania))) +
LTRIM(RTRIM(@.FechaDesde)) + LTRIM(RTRIM(@.FechaHasta)) +
LTRIM(RTRIM(STR(@.Precio)))
SET @.NombreTablaTemporal = LTRIM(RTRIM( replace(@.NombreTablaTemporal, '-',
'_') ))
SET @.NombreTablaTemporal = 'BORRAR4'
if NOT exists (select * from sysobjects where name = @.NombreTablaTemporal )
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.STRINGINSERT = 'INSERT INTO ' + @.NombreTablaTemporal + ' (
IDPVSRC, nombreIDPVSRC , CIUDAD, IDPVP, PVP, URLMULTIMEDIA , Ancho, Alto) '
SET @.STRINGINSERT = @.STRINGINSERT + 'VALUES (' +
RTRIM(LTRIM(STR(@.IDPVSRC))) + ', '
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNombre)) + '" ,'
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNomCiudad)) + '" , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(STR(@.IDPVP)))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.PVP))) + ' , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(@.IDPVSRCUrl))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAncho)))
+ ' , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAlto)))
SET @.STRINGINSERT = @.STRINGINSERT + ' ) '
-- @.STRINGINSERT
EXEC (@.STRINGINSERT)
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
END
CLOSE Cursor1
DEALLOCATE Cursor1
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
RETURN @.@.ROWCOUNT
END
ELSE
BEGIN
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
END
GO
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:ejJalkYlFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Ral,
> First of all, you must change the name of your stored procedure.
> Procedures named sp_* are treated differently by the query
> processor. This name is used to indicate that there is a system
> stored procedure in the master database available from all databases,
> so the processor must first search the objects in master, find no
> system procedure, then look for your procedure in your database.
> If this doesn't solve the problem, consider adding WITH RECOMPILE
> to the definition of the stored procedure. What you may be seeing is
> the result of a cached plan for the stored procedure that is optimal
> for one set of parameters and very bad for another. When you run
> this query outside a procedure, the plan will not be cached, but
> will be recompiled for the particular variable values.
> If that still doesn't work, try declaring local variables for your
> parameters and reassigning them inside the procedure:
> ...
> declare @.Campania2 int,
> @.FechaDesde2 nchar(10),
> @.FechaHasta2 nchar(10),
> @.Pais2 nchar(5),
> @.Ciudad2 nchar(5),
> @.Duracion2 int,
> @.Precio2 int
> select
> @.Campania2 = @.Campania,
> @.FechaDesde2 = @.FechaHasta,
> ...
> This sometimes improves the choice of plan with or
> without adding WITH RECOMPILE. In your case, it
> might also be worthwhile to use local datetime variables for
> the result of CONVERT(DATETIME, @.FechaHasta, 102),
> CONVERT(DATETIME, @.FechaDesde, 102), and
> CONVERT(DATETIME, GETDATE(), 102)
> Queries this complicated with many parameters can be difficult
> to optimize, particularly if the optimal query plan depends on
> the particular parameter values.
> Finally, I didn't look closely, but I don't quite understand the
> purpose of
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
> TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
> Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> instead of just putting .Multimedias.IDtipoMultimedia = 0 into
> the where clause before grouping. I'm also not sure why the
> extra . before Multimedias.
>
> Steve Kass
> Drew University
>
> Ral Martn wrote:
>
seconds
the
2)
2)
(IDPVSRC
smallint)'
,
MIXG.Ciudad
IDPVSRC,
AS
PreciosXFechasPVSRC.IDPVSRC
AND
MIX.IDvuelo,
IDPVP,
@.FechaHasta,
TABLAPVP.PVP|||Sorry, the correct store procedure is like that
(without cursors)
CREATE PROCEDURE BuscardorOfertasmejorCIUDADESsincursor
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
RETURN @.@.ROWCOUNT
END
GO
I have a sql that return about 35 rows
If I execute that sql in the analizer it spend 4 seconds (with
variables)
If I execute the same sql in a store procedure it spend 35 seconds
(with parameters)
The size of the variables or parameters are the same as the columns of the
tables so...
Why it can be?
This is the store procedure
CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
@.Campania int,
@.FechaDesde nchar(10),
@.FechaHasta nchar(10),
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2)
+ '-' + Left(@.FechaDesde, 2)
SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2)
+ '-' + Left(@.FechaHasta, 2)
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
-- DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <=
CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >=
CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
@.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
ORDER BY TGENERAL.PVP
END
RETURN @.@.ROWCOUNT
GORal,
First of all, you must change the name of your stored procedure.
Procedures named sp_* are treated differently by the query
processor. This name is used to indicate that there is a system
stored procedure in the master database available from all databases,
so the processor must first search the objects in master, find no
system procedure, then look for your procedure in your database.
If this doesn't solve the problem, consider adding WITH RECOMPILE
to the definition of the stored procedure. What you may be seeing is
the result of a cached plan for the stored procedure that is optimal
for one set of parameters and very bad for another. When you run
this query outside a procedure, the plan will not be cached, but
will be recompiled for the particular variable values.
If that still doesn't work, try declaring local variables for your
parameters and reassigning them inside the procedure:
...
declare @.Campania2 int,
@.FechaDesde2 nchar(10),
@.FechaHasta2 nchar(10),
@.Pais2 nchar(5),
@.Ciudad2 nchar(5),
@.Duracion2 int,
@.Precio2 int
select
@.Campania2 = @.Campania,
@.FechaDesde2 = @.FechaHasta,
..
This sometimes improves the choice of plan with or
without adding WITH RECOMPILE. In your case, it
might also be worthwhile to use local datetime variables for
the result of CONVERT(DATETIME, @.FechaHasta, 102),
CONVERT(DATETIME, @.FechaDesde, 102), and
CONVERT(DATETIME, GETDATE(), 102)
Queries this complicated with many parameters can be difficult
to optimize, particularly if the optimal query plan depends on
the particular parameter values.
Finally, I didn't look closely, but I don't quite understand the
purpose of
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
instead of just putting .Multimedias.IDtipoMultimedia = 0 into
the where clause before grouping. I'm also not sure why the
extra . before Multimedias.
Steve Kass
Drew University
Ral Martn wrote:
>Hi
> I have a sql that return about 35 rows
> If I execute that sql in the analizer it spend 4 seconds (with
>variables)
> If I execute the same sql in a store procedure it spend 35 seconds
>(with parameters)
>The size of the variables or parameters are the same as the columns of the
>tables so...
> Why it can be?
>This is the store procedure
>CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
> @.Campania int,
> @.FechaDesde nchar(10),
> @.FechaHasta nchar(10),
> @.Pais nchar(5),
> @.Ciudad nchar(5),
> @.Duracion int,
> @.Precio int
>AS
>
>DECLARE @.PrecioMin int, @.PrecioMax int
>SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2
)
>+ '-' + Left(@.FechaDesde, 2)
>SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2
)
>+ '-' + Left(@.FechaHasta, 2)
>DECLARE @.TablaAcrear varchar(2000)
>DECLARE @.STRINGINSERT varchar(4000)
>DECLARE @.STRINGadevolver varchar(1000)
>DECLARE @.NombreTablaTemporal nvarchar(250)
>BEGIN
>
> SET @.PrecioMin = 0
> SET @.PrecioMax = 999999
> IF @.Precio = 1
> BEGIN
> SET @.PrecioMin = 0
> SET @.PrecioMax = 300
> END
> ELSE IF @.Precio = 2
> BEGIN
> SET @.PrecioMin = 301
> SET @.PrecioMax = 600
> END
> ELSE IF @.Precio = 3
> BEGIN
> SET @.PrecioMin = 601
> SET @.PrecioMax = 900
> END
> ELSE IF @.Precio = 4
> BEGIN
> SET @.PrecioMin = 901
> SET @.PrecioMax = 1200
> END
> ELSE IF @.Precio = 5
> BEGIN
> SET @.PrecioMin = 1201
> SET @.PrecioMax = 999999
> END
>
> DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
>decimal(9,0),
> @.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
>@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
> DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
>@.IDduracion int
> DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
>
> set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
>int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
>decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
> exec (@.TablaAcrear)
>
> -- CIUDADES
>
> -- DECLARE Cursor1 CURSOR FOR
>
> SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
>PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
>TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
>TGENERAL.IDduracion as IDduracion,
> TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
>Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
>Multimedias.alto as alto
> FROM (
> SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
>TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
> MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
>AS Ciudad
> FROM (
> SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
>MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
> MIX.IDduracion, MIX.nombreIDPVSRC AS
>nombreIDPVSRC, MIX.nombre AS Ciudad
> FROM (
> SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
>PVSRC.IDvuelo,
> PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
>Ciudades.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
>INNER JOIN
> Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
> Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
> CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
> WHERE
> (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
>(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AN
D
>(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
>(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
>(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
>@.Campania)
> AND (Ciudades.IDCIUDAD = @.Ciudad)
> GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
> PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
> )
> MIX
> INNER JOIN
> (
> SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
>PreciosXFechasPVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <=
>CONVERT(DATETIME, @.FechaHasta, 102)) AND
> (PreciosXFechasPVSRC.fechaBaja >=
>CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
>@.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> )
> TABLAPVSRC
> ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
>TABLAPVSRC.nombre
> GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
>MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
> ORDER BY MIX.PVP
> )
> MIXG
> INNER JOIN
> (
> SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
>PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
> PVSRC.IDpaquete
> FROM PreciosXFechasPVSRC INNER JOIN
> PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
>102)) AND
> (PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
>102)) AND
> (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
>PVSRC.IDpaquete
> )
> TABLAPVP
> ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
> )
> TGENERAL
> INNER JOIN PVSRC
> ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
> INNER JOIN
> .Multimedias ON .PVSRC.IDmultimedia =
>.Multimedias.IDmultimedia
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
>TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
>Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> ORDER BY TGENERAL.PVP
>
> END
>
>RETURN @.@.ROWCOUNT
>GO
>
>|||Thanks a lot Steve but with those changes it is worse than before.
I modified de "sp_" , added WITH RECOMPILE, added local parameters and I
modified the query
at the end with
WHERE (.Multimedias.IDtipoMultimedia = 0)
in stead of
HAVING (.Multimedias.IDtipoMultimedia = 0)
but now it spend 80 seconds so if you have more ideas...
now the store procedure is:
CREATE PROCEDURE BuscardorOfertasmejorCIUDADES
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
DROP TABLE BORRAR4
SET @.NombreTablaTemporal = '_tempBusc' + LTRIM(RTRIM(@.Pais)) +
LTRIM(RTRIM(@.Ciudad)) + LTRIM(RTRIM(STR(@.Campania))) +
LTRIM(RTRIM(@.FechaDesde)) + LTRIM(RTRIM(@.FechaHasta)) +
LTRIM(RTRIM(STR(@.Precio)))
SET @.NombreTablaTemporal = LTRIM(RTRIM( replace(@.NombreTablaTemporal, '-',
'_') ))
SET @.NombreTablaTemporal = 'BORRAR4'
if NOT exists (select * from sysobjects where name = @.NombreTablaTemporal )
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.STRINGINSERT = 'INSERT INTO ' + @.NombreTablaTemporal + ' (
IDPVSRC, nombreIDPVSRC , CIUDAD, IDPVP, PVP, URLMULTIMEDIA , Ancho, Alto) '
SET @.STRINGINSERT = @.STRINGINSERT + 'VALUES (' +
RTRIM(LTRIM(STR(@.IDPVSRC))) + ', '
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNombre)) + '" ,'
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNomCiudad)) + '" , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(STR(@.IDPVP)))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.PVP))) + ' , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(@.IDPVSRCUrl))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAncho)))
+ ' , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAlto)))
SET @.STRINGINSERT = @.STRINGINSERT + ' ) '
-- @.STRINGINSERT
EXEC (@.STRINGINSERT)
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
END
CLOSE Cursor1
DEALLOCATE Cursor1
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
RETURN @.@.ROWCOUNT
END
ELSE
BEGIN
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
END
GO
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:ejJalkYlFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Ral,
> First of all, you must change the name of your stored procedure.
> Procedures named sp_* are treated differently by the query
> processor. This name is used to indicate that there is a system
> stored procedure in the master database available from all databases,
> so the processor must first search the objects in master, find no
> system procedure, then look for your procedure in your database.
> If this doesn't solve the problem, consider adding WITH RECOMPILE
> to the definition of the stored procedure. What you may be seeing is
> the result of a cached plan for the stored procedure that is optimal
> for one set of parameters and very bad for another. When you run
> this query outside a procedure, the plan will not be cached, but
> will be recompiled for the particular variable values.
> If that still doesn't work, try declaring local variables for your
> parameters and reassigning them inside the procedure:
> ...
> declare @.Campania2 int,
> @.FechaDesde2 nchar(10),
> @.FechaHasta2 nchar(10),
> @.Pais2 nchar(5),
> @.Ciudad2 nchar(5),
> @.Duracion2 int,
> @.Precio2 int
> select
> @.Campania2 = @.Campania,
> @.FechaDesde2 = @.FechaHasta,
> ...
> This sometimes improves the choice of plan with or
> without adding WITH RECOMPILE. In your case, it
> might also be worthwhile to use local datetime variables for
> the result of CONVERT(DATETIME, @.FechaHasta, 102),
> CONVERT(DATETIME, @.FechaDesde, 102), and
> CONVERT(DATETIME, GETDATE(), 102)
> Queries this complicated with many parameters can be difficult
> to optimize, particularly if the optimal query plan depends on
> the particular parameter values.
> Finally, I didn't look closely, but I don't quite understand the
> purpose of
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
> TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
> Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> instead of just putting .Multimedias.IDtipoMultimedia = 0 into
> the where clause before grouping. I'm also not sure why the
> extra . before Multimedias.
>
> Steve Kass
> Drew University
>
> Ral Martn wrote:
>
seconds
the
2)
2)
(IDPVSRC
smallint)'
,
MIXG.Ciudad
IDPVSRC,
AS
PreciosXFechasPVSRC.IDPVSRC
AND
MIX.IDvuelo,
IDPVP,
@.FechaHasta,
TABLAPVP.PVP|||Sorry, the correct store procedure is like that
(without cursors)
CREATE PROCEDURE BuscardorOfertasmejorCIUDADESsincursor
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
RETURN @.@.ROWCOUNT
END
GO
Subscribe to:
Posts (Atom)