Sql View Very Very Slow The First Run And Then Super Fast... Why?
Nov 2, 2007
Hi,
I have a query that takes 15minutes to run the first time after sql server start but then for subsequent querys is superfast (less than 10 secs).
Why? What could I do to speed up the first run?
Here is the source:
Code Block
SELECT Articoli.Articolo, Articoli.BarCodeVen, Articoli.Descri, Articoli.Merce, Articoli.CodIVA, Articoli.UMBase, Articoli.PrzBase, Articoli.Sconto1, Articoli.Sconto2,
Articoli.ScontoFormula, Articoli.Ricarico, Articoli.TipoArt, Articoli.CatMerc, Articoli.CatOmo, Articoli.CatProvv, Articoli.CntrprtVen, Articoli.CntrprtAcq,
Articoli.Dispniblta, Articoli.Vendita, Articoli.SiCli, Articoli.SiFor, Articoli.NotaPriv, Articoli.NotaPubb, Articoli.Immagine, Articoli.Produttore, Articoli.SiMatr,
Articoli.OldCodArt, Articoli.Disattivo, Articoli.CatProd, Articoli.SotCatProd, Articoli.EsplKit, Articoli.ScaricoCom, Articoli.ImmagLun, Articoli.DataModCS,
Articoli.ValorizPr, Articoli.Natura, Articoli.UMSecScelta, Articoli.SecScelta, Articoli.Acquisto, Articoli.CntConsumi, Articoli.NonMovimentabile,
Articoli.ProvAgente, Articoli.Taglia, Articoli.Colore, Articoli.CodArtNeutro, Articoli.Collezione, Articoli.Assortimento, Articoli.Centro,
Articoli.InibisciRicercaUnMis, Articoli.Commessa, Articoli.DescriTesto, Articoli.Disattivabile, Articoli.KwTipoArt, Articoli.KwTipoArtNeutro,
Articoli.KwNeutro, Articoli.KwCollezione, Articoli.KwLinea, Articoli.KwModello, Articoli.KwMateriale, Articoli.KwComposizione, Articoli.KwPosRiga,
Articoli.KwVarRiga, Articoli.KwPosColonna, Articoli.KwVarColonna, Articoli.KwPos3D, Articoli.KwVar3D, Articoli.KwGrpVarRiga, Articoli.KwGrpVarColonna,
Articoli.KwGrpVar3D, Articoli.KwEtiMan, Articoli.KwAssortimento, Articoli.KwGrpFormule, Articoli.KwSpecimen, KwVarArticolo.TipoVariante,
KwVarArticolo.Variante, KwVarArticolo.Posizione, KwVarArticolo.Categoria, KwVarArticolo.DescriVariante,
KwVarArticolo.P07009Descrizione1 AS Descrizione1, KwVarArticolo.P07009Descrizione2 AS Descrizione2, CatOmo.Descri AS CatOmoDescri,
KwModelli.Descri AS DescriModello, KwMateriali.Descri AS DescriMateriale, MFDBCompon_Tomaia2.DatoTecnico,
MFDBCompon_Tomaia2.DBPosColonna, MFDBCompon_Tomaia2.DBPos3D, MFDBCompon_Tomaia2.CompNeutro,
MFDBCompon_Tomaia2.CompVarRiga, Articoli_1.Descri AS DescriCompNeutro, MFKwVariantiRiga.DescriVariante AS DescriRigaCompo,
CatProvv.Descri AS CatProvvDescri, MFDBCompon_Tomaia3.CompNeutro AS CompNeutroT3,
MFDBCompon_Tomaia3.CompVarRiga AS CompVarrigaT3, Articoli_2.Descri AS DescriCompNeutroT3,
MFKwVariantiRiga_1.DescriVariante AS DescriRigaCompoT3, MFDBCompon_Acc1.CompNeutro AS CompNeutroAcc1,
MFDBCompon_Acc1.CompVarRiga AS CompVarRigaAcc1, Articoli_3.Descri AS DescriCompNeutroAcc1,
MFKwVariantiRiga_2.DescriVariante AS DescriRigaCompoAcc1, MFDBCompon_Acc2.CompNeutro AS CompNeutroAcc2,
MFDBCompon_Acc2.CompVarRiga AS CompVarRigaAcc2, Articoli_4.Descri AS DescriCompNeutroAcc2,
MFKwVariantiRiga_3.DescriVariante AS DescriRigaCompoAcc2, MFDBCompon_SottoP_Tallonet.CompNeutro AS CompNeutroSottop,
MFDBCompon_SottoP_Tallonet.CompVarRiga AS CompVarRigaSottop, MFDBCompon_SottoP_Tallonet.DescriCompNeutroSottop,
MFDBCompon_SottoP_Tallonet.DescriRigaCompoSottop, MFDBCompon_SottoP_Tallonet.DatoTecnico AS DatoTecnicoSottop,
MFDBCompon_RicSol.CompNeutro AS CompNeutroRicSol, MFDBCompon_RicSol.CompVarRiga AS CompVarRigaRicSol,
MFDBCompon_RicSol.DescriCompNeutroRicSol, MFDBCompon_RicSol.DescriRigaCompoRicSol,
MFDBCompon_Fodera.CompNeutro AS CompNeutroFodera, MFDBCompon_Fodera.CompVarRiga AS CompVarRigaFodera,
MFDBCompon_Fodera.DescriCompNeutroFodera, MFDBCompon_Fodera.DescriRigaCompoFodera, MFDBCompon_Forma.Descri AS DescriForma,
MFDBCompon_Tacco.Descri AS DescriTacco, MFVar3D_Pos1.DescriVariante AS Descri_Pos1, MFVar3D_Pos2.DescriVariante AS Descri_Pos2,
MFVar3D_Pos3.DescriVariante AS Descri_Pos3, MFVar3D_Pos4.DescriVariante AS Descri_Pos4, MFListinoP00New.Prz AS P00,
MFListinoP00New.InVigoreDa AS DataP00, MFListinoP05New.Prz AS P05, MFListinoP05New.InVigoreDa AS DataP05, MFListinoPUSANew.Prz AS PUSA,
MFListinoPUSANew.InVigoreDa AS DataPUSA,
CASE WHEN KwVarArticolo.P07009Descrizione2 <> '' THEN KwVarArticolo.P07009Descrizione2 ELSE CASE WHEN Articoli.CatProvv = '30SPORT' THEN Articoli.KwNeutro
+ KwVarArticolo.Variante + '.jpg' ELSE Articoli.KwNeutro + '.jpg' END END AS MFFoto, MFListinoRUSANew.Prz AS RUSA,
MFListinoRUSANew.InVigoreDa AS DataRUSA, MFListinoRITANew.Prz AS RITA, MFListinoRITANew.InVigoreDa AS DataRITA, ArtMerce.Fornitore
FROM Articoli INNER JOIN
KwVarArticolo ON Articoli.Articolo = KwVarArticolo.Articolo INNER JOIN
CatOmo ON Articoli.CatOmo = CatOmo.Categoria INNER JOIN
KwMateriali ON Articoli.KwModello = KwMateriali.KwModello AND Articoli.KwMateriale = KwMateriali.KwMateriale INNER JOIN
KwModelli ON Articoli.KwModello = KwModelli.KwModello AND Articoli.KwMateriale = KwModelli.KwMateriale LEFT OUTER JOIN
ArtMerce ON Articoli.Articolo = ArtMerce.Articolo LEFT OUTER JOIN
MFListinoRUSANew ON KwVarArticolo.Variante = MFListinoRUSANew.VarRiga AND Articoli.KwNeutro = MFListinoRUSANew.Neutro LEFT OUTER JOIN
MFListinoRITANew ON KwVarArticolo.Variante = MFListinoRITANew.VarRiga AND Articoli.KwNeutro = MFListinoRITANew.Neutro LEFT OUTER JOIN
MFListinoPUSANew ON KwVarArticolo.Variante = MFListinoPUSANew.VarRiga AND Articoli.KwNeutro = MFListinoPUSANew.Neutro LEFT OUTER JOIN
MFListinoP05New ON KwVarArticolo.Variante = MFListinoP05New.VarRiga AND Articoli.KwNeutro = MFListinoP05New.Neutro LEFT OUTER JOIN
MFListinoP00New ON Articoli.KwNeutro = MFListinoP00New.Neutro AND KwVarArticolo.Variante = MFListinoP00New.VarRiga LEFT OUTER JOIN
MFVar3D_Pos4 ON Articoli.KwNeutro = MFVar3D_Pos4.Articolo LEFT OUTER JOIN
MFVar3D_Pos3 ON Articoli.KwNeutro = MFVar3D_Pos3.Articolo LEFT OUTER JOIN
MFVar3D_Pos2 ON Articoli.KwNeutro = MFVar3D_Pos2.Articolo LEFT OUTER JOIN
MFVar3D_Pos1 ON Articoli.KwNeutro = MFVar3D_Pos1.Articolo LEFT OUTER JOIN
MFDBCompon_Forma ON KwVarArticolo.Variante = MFDBCompon_Forma.DBVarRiga AND
Articoli.Articolo = MFDBCompon_Forma.DBNeutro LEFT OUTER JOIN
MFDBCompon_Tacco ON KwVarArticolo.Variante = MFDBCompon_Tacco.DBVarRiga AND
Articoli.Articolo = MFDBCompon_Tacco.DBNeutro LEFT OUTER JOIN
MFDBCompon_Fodera ON KwVarArticolo.Variante = MFDBCompon_Fodera.DBVarRiga AND
Articoli.KwNeutro = MFDBCompon_Fodera.DBNeutro LEFT OUTER JOIN
MFDBCompon_RicSol ON KwVarArticolo.Variante = MFDBCompon_RicSol.DBVarRiga AND
Articoli.KwNeutro = MFDBCompon_RicSol.DBNeutro LEFT OUTER JOIN
MFDBCompon_SottoP_Tallonet ON KwVarArticolo.Variante = MFDBCompon_SottoP_Tallonet.DBVarRiga AND
Articoli.KwNeutro = MFDBCompon_SottoP_Tallonet.DBNeutro LEFT OUTER JOIN
MFDBCompon_Acc2 INNER JOIN
Articoli AS Articoli_4 ON MFDBCompon_Acc2.CompNeutro = Articoli_4.Articolo INNER JOIN
MFKwVariantiRiga AS MFKwVariantiRiga_3 ON MFDBCompon_Acc2.CompVarRiga = MFKwVariantiRiga_3.Variante ON
KwVarArticolo.Variante = MFDBCompon_Acc2.DBVarRiga AND Articoli.KwNeutro = MFDBCompon_Acc2.DBNeutro LEFT OUTER JOIN
MFDBCompon_Acc1 INNER JOIN
Articoli AS Articoli_3 ON MFDBCompon_Acc1.CompNeutro = Articoli_3.Articolo INNER JOIN
MFKwVariantiRiga AS MFKwVariantiRiga_2 ON MFDBCompon_Acc1.CompVarRiga = MFKwVariantiRiga_2.Variante ON
KwVarArticolo.Variante = MFDBCompon_Acc1.DBVarRiga AND Articoli.KwNeutro = MFDBCompon_Acc1.DBNeutro LEFT OUTER JOIN
MFDBCompon_Tomaia3 INNER JOIN
Articoli AS Articoli_2 ON MFDBCompon_Tomaia3.CompNeutro = Articoli_2.Articolo INNER JOIN
MFKwVariantiRiga AS MFKwVariantiRiga_1 ON MFDBCompon_Tomaia3.CompVarRiga = MFKwVariantiRiga_1.Variante ON
KwVarArticolo.Variante = MFDBCompon_Tomaia3.DBVarRiga AND Articoli.KwNeutro = MFDBCompon_Tomaia3.DBNeutro LEFT OUTER JOIN
CatProvv ON Articoli.CatProvv = CatProvv.Categoria LEFT OUTER JOIN
Articoli AS Articoli_1 INNER JOIN
MFDBCompon_Tomaia2 ON Articoli_1.Articolo = MFDBCompon_Tomaia2.CompNeutro INNER JOIN
MFKwVariantiRiga ON MFDBCompon_Tomaia2.CompVarRiga = MFKwVariantiRiga.Variante ON
KwVarArticolo.Variante = MFDBCompon_Tomaia2.DBVarRiga AND Articoli.KwNeutro = MFDBCompon_Tomaia2.DBNeutro
WHERE (Articoli.KwCollezione = @KwCollezione) AND (Articoli.KwLinea = @KwLinea) AND (KwVarArticolo.TipoVariante = 52559872) AND
(Articoli.CatProvv IN (@CatProvv)) AND (Articoli.Disattivo = '0') AND (KwVarArticolo.Disattivo = '0') AND (ArtMerce.Fornitore IN (@Fornitore)) OR
(Articoli.KwCollezione = @KwCollezione) AND (Articoli.KwLinea = @KwLinea) AND (KwVarArticolo.TipoVariante = 52559872) AND
(Articoli.CatProvv IN (@CatProvv)) AND (Articoli.Disattivo = '0') AND (KwVarArticolo.Disattivo = '0') AND ('ALL' IN (@Fornitore)) OR
(Articoli.KwCollezione = @KwCollezione) AND (Articoli.KwLinea = @KwLinea) AND (KwVarArticolo.TipoVariante = 52559872) AND (Articoli.Disattivo = '0')
AND (KwVarArticolo.Disattivo = '0') AND (ArtMerce.Fornitore IN (@Fornitore)) AND ('ALL' IN (@CatProvv)) OR
(Articoli.KwCollezione = @KwCollezione) AND (Articoli.KwLinea = @KwLinea) AND (KwVarArticolo.TipoVariante = 52559872) AND (Articoli.Disattivo = '0')
AND (KwVarArticolo.Disattivo = '0') AND ('ALL' IN (@CatProvv)) AND ('ALL' IN (@Fornitore))
ORDER BY Articoli.CatProvv, Articoli.CatOmo, Articoli.Articolo, KwVarArticolo.Posizione
we just moved a database from a shared SQL 2000 server to SQLExpress on a VPS server. Every thing seems to work great, really no performance loss at all, except for one stored proc that is giving us problems. It's function is to update a history table, and then update the production table. On the old server, it would take less than a second to run this, now it takes anywhere from 45 seconds to 1 minute or it times out. This database is used on a classic asp web app. ANY help at all on this would be appreciated as I am pulling my hair out trying to figure out what's wrong here. here is the proc.
------------------------------------------ code ---------------------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON go
I have a very puzzling situation with a database. It's an Access 2000 mdbwith a SQL 7 back end, with forms bound using ODBC linked tables. At ourremote location (accessed via a T1 line) the time it took to go to a recordwas very slow. The go to mechanism was a box that the user typed the indexvalue into a combo box, with very simple code attached:with me.RecordsetClone.FindFirst "[Index] = " & me.cboGoToIf Not .NoMatch ThenMe.Bookmark = .BookmarkEnd Ifend withNow, one would say that going to a record is slow because I'm using..FindFirst over a T1 line. And that's what I thought. However, as I wasworking with the form, commenting out various sections not related to the GoTo, I found that the Go To functionality changed, though I didn't modify thecode.Previously, going to a record near the end of the 50,000 record recordsettook about 1-2 seconds, but going to a record near the beginning, took about20 seconds. After the form changed, going to any record in the recordsettook about 1-2 seconds.So the question remains: why did it take so long to go to a record near thebeginning of the recordset, but not near the end (and the ones in the middletook an amount of time about halfway between the two), and what changed sothat now the form is working fine for all records?I've compared the changed form with the previous copy, and I don't see anydifferences. I've compared all code in the form module, and I've comparedall form properties. The forms are identical as far as I could tell. Butsomething happened as I was commenting/uncommenting code in the form thatgot rid of the problem with it taking a long time to go to some of therecords.My first thought was that something got recompiled, and now the form isfast. So I went back to the original version and changed some code andrecompiled, also did a compact and repair. But it was still slow. I alsotried doing an explicit decompile and then recompiled it. But it was stillslow.So this is very frustrating that the form is now working fine, but I can'tsee anything that's changed. If I don't see why the form is now fast, thenthere's no reason to believe that it might not at some point go back tobeing slow again. And then I'd just have to hope that something changes. Itwould be good to figure this out.Any ideas as to what might have changed here to cause the form's Go To to befast would be appreciated.Thanks,Neil
I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.
I have notised that when I run the package via right-clicking the package (Under stored packagesMSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".
I'm trying to figure out how to determine why the same database is fast on my desktop but slow on a robust multiprocessor win2003 server.
I have a complex proc that takes about 27 seconds on my desktop sql server but takes almost 5 minutes on the server. The server processors are running at about 1% utilization while the proc is running.
Any ideas on how to troubleshoot this extremely poor performance?
Hi! we've got a curious problem. we've two identicall databases, same tables, same date, same indexes, same server, both replicated. One of them is our life database and the other is our development database. If I execute the following query at the development database the query takes less then one second. If I execute the same query at the live database the query takes about 4 seconds to complete. This is very unsatisfactory!! What I've noticed is, if I try to analyze the query in the tuning advisor on the developement database it completes without tuning tips. If I do the same on the live database I get the following error: TITLE: DTAEngine
I have a very unusual situation - we converted a client from DB2 7.2 to MS SQL Server 2000, SP3. There is one report that runs very quickly when ran on the Database Server, but it takes a long time to complete when it is ran from a client system. This query is ran from within the application and not from within Query Analyzer.
Has anyone else here ever encountered this issue? What did it turn out to be? I am leaning away from it being a network issue.
I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System; using System.Collections.Generic; using System.Text; using System.Net; using System.IO;
namespace Predict.Services { public static class Foo { public static string GetIntradayQuote(string symbol) { string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close(); response.Close();
return returnQuote; } } }
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night. It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information: - It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node). - The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access. - When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running. - Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
(Using win2k, sqlserver2k, framework 1.1) I have an fairly data-heavy application that uses Windows authentication (Trusted connection/aspnet account) to connect to Sql Server. The site uses IIS basic authentication.
On the dev server everything works fine but when I move to the live server things get strange and it starts to crawl along. (Pages load OK but then it just crawls as it loads the datagrids etc. Sometimes it brings back incomplete/incorrect data )
BUT When I use Sql Authentication to connect to Sql Server and there is no problem at all!
Ok, there is something obviously wrong with the live server (which is identical setup to dev)but I dont know where to start.
I have the same database on two different servers. One for production and one for testing. A view that I use runs in less than 2 seconds on the test system, but takes almost 2 minutes on the production server.
What I have noticed is on the test server the view will use an index. The production server ends up scanning a whole table. All indices are the same on both machines for the tables involved and I have updated the statistics. I even went through the process of creating a new table with its indices for the table that is being scanned. Both machines have had service pack 1 installed on them.
I previously posted re. this, but thought I'd try again with a summary offacts.I have an Access 2000 MDB with a SQL Server 7 back end. There is a view thatis linked to the database via ODBC and has been in place for several yearswithout any performance problems.Recently I added a couple of fields to the output of the view, and it becamevery slow when scrolling. When just opened in the database window, thelinked view takes about a second to scroll down one screen. When opened inthe form (in Continuous Form view), it takes about 2-3 seconds. It used toscroll just about instantaneously.I tried removing the few fields I added to restore the view to its previousform, but it had no effect. The view was still much slower than it had been.The total number of records returned from the view is about 1300, so it'snot a large number of records. The view has about 25 fields.I found that when I link the view in the MDB without specifying a uniqueindex, it scrolls very quickly -- almost instantaneously. But when I specifythe unique index, it is slow. Since the view needs to be edited, it needsthe unique index defined.As noted, it's been in place for years, with a unique index defined, yetwithout the slowness. Any ideas as to what might have caused this and whatmight be done would be appreciated. I've included the SQL for the viewbelow.Thanks,NeilSQL FOR MAIN VIEW:SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,INVTRY.attFirstEdition, INVTRY.attSigned,ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,INVTRY.PRICE, INVTRY.Web, INVTRY.Status,INVTRY.WebStatusPending, INVTRY.ActivateDate,INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,INVTRY.AllowDuplicate, INVTRY.WebAction,INVTRY.WebActionPending, INVTRY.DateModified,INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,INVTRY.HImage, INVTRY.AdCode,CASE WHEN INVTRY.WebAddedBatchID IS NOT NULLTHEN - 1 ELSE 0 END AS OnWebFROM vwInventory_Dupes INNER JOIN(WebStatus INNER JOIN(INVTRY INNER JOINtabStatus ON INVTRY.Status = tabStatus.Status) ONWebStatus.WebStatus = INVTRY.Web) ON(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND(vwInventory_Dupes.TITLE = INVTRY.TITLE)WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)= 1))SQL FOR vwInventory_Dupes:SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,Cast(attFirstEdition AS tinyint) FirstEd,Cast(attSigned AS tinyint) Signed,ISNULL(INVTRY.attSignedPD, ' ') SignedCond,INVTRY.YRPUB YearPubFROM WebStatus INNER JOIN(INVTRY INNER JOINtabStatus ON INVTRY.Status = tabStatus.Status) ONWebStatus.WebStatus = INVTRY.WebWHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)= 1))GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUBHAVING (((COUNT(INVTRY.[INDEX])) > 1))
HiI have a procedure that calls a view. The view is built with some outerjoins but it performs fine.If I run in Query Analyzerselect count(*) from long_name_viewwhere name_id = 'AAA'it returns instantlyThe procedure has the same code. I juststripped down the code to narrowthe problem:create or replace procedure my_name_proc@nid VARCHAR(32)ASDECLARE@nidkey_count INTEGERselect @nidkey_count = count(*)from long_name_viewwhere name_id = @nidprint 'The count: ' + CAST(@nidkey_count as varchar)GOWhen I call in Query Analyzer:exec my_name_proc 'AAA'it takes a while to run, over 20 sec and the execution plan isdifferent. What is the reason that the same view is used in differentways?Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
when i run the query it takes less that 5 seconds to give results
but when i run the view it takes about a minute
can anyone help please, it keeps giving me time out errors
here is the query.
SELECT MAX(B.Code) AS Code, MAX( T.Description) AS Type, MAX( WH.Description) AS Warehouse, MAX(B.Barcode) AS Barcode, MAX(B.BatchNo) AS BatchNo, MAX(B.CustomField) AS CustomField, MAX(B.Colour) AS Colour, MAX(Q.Quality) AS Quality, MAX(round( M.ConvFactor, 2, 2)) AS ConvFactor, MAX( M.Multiply) AS Multiply, MAX(CONVERT(VARCHAR(20), round((B.BoughtQty + B.TransferQty + B.IssuedQty + B.ReturnedQty + B.AdjustmentQty), 2))) + ' ' + MAX( M.UoM) AS Available, CASE WHEN MAX( M.Multiply) = 'M' THEN MAX(CONVERT(VARCHAR(20), round(((B.BoughtQty + B.TransferQty + B.IssuedQty + B.ReturnedQty + B.AdjustmentQty) * M.ConvFactor), 2))) + ' ' + MAX(M.AUoM) ELSE max(CONVERT(Varchar(50),convert(float, round(((B.BoughtQty + B.TransferQty + B.IssuedQty + B.ReturnedQty + B.AdjustmentQty) / M.ConvFactor), 2))) ) + ' ' + MAX( M.AUoM) END AS AvailableAlternative, MAX(BC.Supplier) AS Supplier, MAX( OD.SupplierCode) AS SupplierCode FROM cvrbatches B LEFT JOIN SciposA.dbo.cvrmaster M ON M.Code = B.Code LEFT JOIN cvrbatchctrl BC ON B.Code = BC.Code AND B.Type = BC.Type AND B.BatchNo = BC.BatchNo INNER JOIN SciposA.dbo.cvrwhcontrol WH ON WH.Warehouse = B.Warehouse INNER JOIN SciposA.dbo.cvrtypes T ON T.CoverType = B.Type INNER JOIN cvrquality Q ON Q.Code = B.Quality AND B.Type = Q.Type LEFT JOIN SciposA.dbo.cvrgrndetail GD ON GD.BatchNo = B.BatchNo AND B.Warehouse = GD.Warehouse LEFT JOIN SciposA.dbo.cvrorderdetails OD ON OD.OrderNo = GD.OrderNo AND OD.LineN = GD.LineN GROUP BY B.Barcode
So we got ServerA.dbA and ServerB.dbB and looks like nobody thought about that MVew. IT works thru linked server now, so surely enough it sucks..
Then even more: we got piece of DW on ServerRep with replcicated pieces of ServerA and ServerB, how it works I asked: "Don't worry man, we took care of everything, go directly to ServerRep for everything..."
And I see that our MView sucks even deeper, practically 0 performance, I think what going on it's still hit that linked server between ServerA.dbA and ServerB.dbB on background.I still can use it on ServerRep WITH NOEXPAND hint, which means it was properly defined...
Can we have that scenario at all? Or we can replicate all Tables on ServerRep and then define/redefine this view directly on replicated server ?
I am running A View that INSERTS into #Temp Table - On Only Certain Days the INSERT Speed into #tempDB is so slow. Â Attached snapshot that shows after one minute so many few records are inserted - and it dosent happen every day somedays its very fast.Â
I'm use to working with MSaccess and coldfusion and am trying to convert to sql. I ran the upsize wizard in access and now see my DB in the SQL Query Analyzer. How do I open / modify / view the DB? I have zero experience with sql so treat me like a 5 year old.
And if anyone has any knowledge of connecting coldfusion with sql that would also help.
Begging your patience, I'm wondering if someone can help me with a beginner question. I am trying to write a query against our trouble ticket DB. I want to find tickets that have multiple tech assignments associated with a single ticket. How can write a query that says if in the CALLID field you find the same value more than once, return those rows and only those rows?
This is probably a no-brainer for most of you, but I'm really really new to sql.
I'm using ms sql 2005 (I think), and I'm making a scheduling program in VB that uses a database hosted on the sql server. Does sql come with archiving tools, or will I need to make something? All the other DBs on the server are 3rd party apps that have a little archiving tool as part of the utilities package. Will I have to make something like that to remove and save the old schedule entries? If so, can anyone point me in the right direction?
Well, I can't backup up my tran log - too big. I knew enough to know you have to back it up, didn't know enough to know that tran log backup is not included in "Full backup". Contractor installation set Growth to "un-restricted". Me, too swamped to delve deeply in time to learn this is a recipe for disaster, and then it was too late - System down because tran log filled up the disk...
(Ironically, this happened the morning of the first day of some training they sent me to - Microsoft's Programming a Microsoft SQL 2000 Database...)
So, some details... The drives on the system : ___ c: size 12G, free 7G ___ d: size 44.9G, free 10M ___ e: size 44.9G, free 13G
Tran log info : ___ filename .... d:Microsoft SQL ServerMSSQLdatahorizon_Log.LDF ___ name ........ horizon_Log ___ size ........ 5606392 ___ maxsize ..... -1 ___ growth ...... 10 ___ status ...... 1081410
(there are 2 because the other totally green newbie dba added 1 the day the system went down...)
I *thought* the addition of the 2nd tran log file meant the 1st file was no longer part of the picture, and that my available free disk space was sufficient to backup the 2nd tran log file.
Clearly I'm missing something - when I try to back up, I get this in SQL server error log : ___ Operating system error 112(There is not enough space on the disk.)
:confused:___ Does that mean it's trying to backup both the 1st & the 2nd tran logs? :confused:___ Or is it only backing up the 2nd log, but trying to use D: (with only 10M)? :confused:___ Or, is it using C: or E:, but tran log backups need some multiple of the size of the actual log, and there's not enough space even though there are multiple G?
I've been reading & googling. So far tried to truncate both files. ( dbcc shrinkfile('horizon_1_log',truncateonly) ) No luck, got these : ___ Cannot shrink log file 2 (horizon_Log) because all logical log files are in use. ___ Cannot shrink log file 3 (horizon_1_Log) because all logical log files are in use.
:confused: :confused:___ How do I get them to be not "in use"? :confused:___ Must the system be down for that? (certainly seems to...)
Also, checked out the EMPTYFILE option. Books Online says : "Migrates all data from the specified file to other files in the same filegroup"
:confused: ___ Migrates to which "other files", exactly? :confused: ___ Is this the right way to go? :confused: ___ If so, how would I proceed after achieving a successul EMPTYFILE operation?
Also... :confused: ___ In the database's Properties dialog, Transaction Log tab, there's a Delete button. Could that button be used to get me out of this difficulty?
Last, but not least... If I'm going about this altogether wrongly, can I get a pointer or two?
Hi, I really need this help urgently. I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email... Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load con1.Open() Dim cmd As New SqlCommand cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)" 'Set the connect the command object should use cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet da.Fill(ds) con1.Close() If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then Dim objEmail As MailMessage = New MailMessage objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg")) objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName") objEmail.Priority = MailPriority.Normal Dim SmtpMail As New SmtpClient("servername") SmtpMail.Send(objEmail) End If End If End Sub Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy) Realli veri urgent Thanks so much for ur'll help
I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!
Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:
Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?
Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.
Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...
Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.
E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail from tblcodes 1, tblelements 2
my system has 2 db's - sql server 2000 & db2 @ separate locations. i have a select query which needs 2 pick up consolidated data from both the tables. also the schema on the db2 has minor changes when compared with the schema on sql server 2000.
while searching on microsoft i came across the technique of creating a linked server. would this be possible 2 implement in my scenario. also would in this case, be advised that i create another view in the db2 server which has changed the db2 schema to the sql server schema format??
One or more files listed in the statement could not be found or could not be initialized. (Microsoft SQL Server, Error: 5009)
I accidentaly created a log file on my drive E:, but every time that I try to delete the log file it keeps on returning the same error. Can someone please help me delete the log file.
hii have over million records in my DB, what is the best way to get the results fast in case i need to get details of an employe name say "robert", if i do it normally it will take long, should i use index or is there any other good way.thanx in advancecheers
Hello everybody . I have 40 GB db running mostly transaction processing. I set up 1. back full backup 2 times a day (takes 30 -40 min) 2. log backup every 15 min 3. custom log shipping 4. We don't won't use Cluster.
Once in while becouse of nethwork, or other problem log shipping fails, so I have to restart log shipping all over starting from restore in stand by mode last full back of my db.IT takes 2-3 hrs just to do this restore !!!
1. So I am asking advice is any way I can bring down time for restore ? 2. Should diffrential backup be taken ? 3. We will not use Custer
hello all !for MS SQL 2000i am having a table with > 100 000 rowsI must clean itDELETE FROM myTable WHERE Name LIKE 'aser%' AND info IS NULLDELETE FROM myTable WHERE Name LIKE 'tuyi%' AND Info = 'ok'DELETE FROM myTable WHERE Name LIKE 'hop%' AND info LIKE 'retro%'.....about 20 DELETE commandswhat is the best way to do it ?thank you
