Remote Scan On Linked Server - Fast SELECT/Slow UPDATE
Aug 14, 2001
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?
Thank you!
View 2 Replies
ADVERTISEMENT
Jan 11, 2002
I have 2 SQL 2000 servers. One has been added as a linked server.
They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain.
Thanks
View 1 Replies
View Related
May 11, 2007
I have a linked server set up on my local SQL 2000 instance. I try and run an update against an SQL 2005 database and it take 29 seconds. I checked the execution plan and it says it takes the entire time on the Remote Scan. Is there something I need to do to speed this up? There is an index on the PK that I am searching against.
View 2 Replies
View Related
Oct 17, 2006
UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,
col8=SR.col8,col9=SR.col9,col10=SR.col10
FROM LNKSQL1.db1.DBO.Table1 CD
join Table2 USRI on USRI.col00 = CD.col00
join table3 SR on USRI.col00 = SR.col00
Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you
View 4 Replies
View Related
Apr 2, 2008
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?
View 8 Replies
View Related
Feb 15, 2006
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.
Thanks in advance.
View 2 Replies
View Related
Sep 20, 2007
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?
View 4 Replies
View Related
Oct 16, 2005
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
View 8 Replies
View Related
Oct 10, 2006
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".
Any idéa of what this depends on?
regards Andreas
View 9 Replies
View Related
Jul 29, 2005
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
View 4 Replies
View Related
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
Thank you!
Roberto
View 3 Replies
View Related
May 30, 2001
I need to push 40k rows daily to an Oracle database. I am on a Win2k Pro box with the 8.1 client and ADO 2.6 loaded. I've configured the linked server with the Oracle as well as the Microsoft provider and it takes over a minute to push just 24 rows, less than 100 bytes each, at it. Any suggestions?
View 1 Replies
View Related
Mar 6, 2001
I've connect some .DBF file to SQL server 7 with linked server option. My table have about 200.000 records. I've tried with ODBC and Jet 4.0 option (found in www.mssqlserver.com) but I've very slow response if I use a "where" in the select instance.
In fact I've the response after 10 seconds and during this seconds the processor go to 100%.
Does anyone found a most speed way to do this?
Thanks,
Andrea
View 1 Replies
View Related
Dec 26, 2003
Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...
However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.
I am very confused as to why there would be such a performance difference and why sql server would run so slow.
I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.
collation compatible (not selected)
Data access (selected)
RPC (not selected)
RPC Out (not selected)
collation name
connection timeout
query timeout
View 1 Replies
View Related
Oct 17, 2006
Hello,
I have 2 servers (say MAINSRV e SECSRV) running SQL2000 Standard SP3 on Windows 2000 Advanced within a NT (!) domain and each server is linked to the other.
My problem is that if I run a query returning few dozens of rows like:
SELECT * FROM MAINSRV.DbName.dbo.TblName TBLA
WHERE Fieldx = 'anyval'
from a client connected to the SECSRV server, it takes something like 35 minutes to complete, while the same query completes in no time when run on clients connected to MAINSRV.
Even the simplest SELECT Count(*) FROM... takes more than one minute from SECSRV while completing in a fraction of second from MAINSRV.
I tried to change the linked server security options (SQL/Windows), but the remote query remains slow.
There are no locks active on the table, both the servers have almost no load (CPU less than 10%, when tested) and the query returns just a few KBytes, so communication overhead will not be the problem.
Any suggestions will be very appreciated, thank you!!!
View 2 Replies
View Related
Jun 14, 2006
Hello can some one explain this to me and give me some advice. I have sqlserver1 and sqlserver2 I have a linked server set up [ipaddress] on both servers. When I pass a variable to the stored proc or the query it takes up to 20 seconds to return 1 row but if I replace my varibles like email='sqlboy@coxnet' and firstname='ted' and lastname='clien' it returns the one row im looking for in about 2 seconds but if I pass email =@email ,fname=@fname,lname=@lname I get the 20 second thing. The query is a stored proc being called from an asp app. I get the same results when I run this in query analyzer.
Here is the query.
DECLARE @email VARCHAR(75),@fname VARCHAR(50),@lname VARCHAR(100)
IF EXISTS (SELECT TOP 1 email
FROM [ipaddress].{database}.dbo.{tablename}--server1
WHERE email = @email )
BEGIN
set nocount on
SELECT TOP 1 email,
FIRSTNAME,
MIDDLENAME,
LASTNAME,
TITLE,
COMPANYNAME,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIP,
COUNTRY
FROM [ipaddress].{database}.dbo.{tablename} --server2
WHERE email in(SELECT EMAIL
FROM [ipaddress].{database}.dbo.{tablename} where email=@email--server1)
AND RecordStatusID ='1' and FirstName=@fname and LastName=@lname
END
ELSE
IF EXISTS (SELECT top 1 email,
FIRSTNAME,
MIDDLEINITIAL,
LASTNAME,
TITLE,
COMPANYNAME,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIP,
COUNTRY
FROM {databases}.dbo.attendees--server1
WHERE email =@email and FirstName=@fname and LastName=@lname)
BEGIN
SELECT TOP 1 email,
firstname,
MIDDLEINITIAL AS MIDDLENAME,
lastname,
title,
companyname,
address1,
address2,
city,
state,
zip,
country
FROM {database}.dbo.attendees --server1
WHERE email in (SELECT DISTINCT email
FROM server1.dbo.ebooksrequests--server1
where email=@email) and email=@email and FirstName=@fname and LastName=@lname
SET NOCOUNT OFF
END
View 4 Replies
View Related
Jan 30, 2008
Hi!
What is the fastest whay to take the resultset
from an temporary table (# table)
and write it to an remote destination (mapped network drive)
initiated from stored proc?
Similar question if I take the resultset and convert it to xml (using FOR XML clause)
and want it written to disk.
Thank you for your ideas ;-)
View 3 Replies
View Related
Mar 13, 2007
I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.
I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.
What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.
I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.
It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?
Thanks in advance for any help on this
View 1 Replies
View Related
Dec 28, 2006
Creating a web application. Running a simple query "SELECT username FROM vwCustomer"
vwCustomer is a view built on top of an Access DB which is set up as a linked server. Within SQL Server that view responds immediately. But when I try to access it from an ASP page it takes over 20 seconds to respond.
Used the following to create the linked server:
EXEC sp_addlinkedserver 'DBName', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:databasesdatabase.mdb'
GO
EXEC sp_addlinkedsrvlogin 'DBName', FALSE, NULL, 'Admin', ''
GO
The view is simply(this works fine in SQL Server):
SELECT * FROM DBName.db.dbo.customer
The ASP page:
SELECT * FROM vwCustomer
Can anyone give me a hint as to where the bottleneck is? Or how I can figure that out using SQL Profiler or something?
Please help!
View 3 Replies
View Related
Jan 2, 2008
What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
View 5 Replies
View Related
Mar 23, 2000
Hello all SQL Server 7 gurus,
I need your help!!! I can use SELECT to query a table on the remote server but
I can't use UPDATE,DELETE OR INSERT. Please tell me how to get around this problem.
Thanks in advance.
PS. The remote server is added by using sp_addlinkedserver
View 7 Replies
View Related
Feb 11, 2005
I need to be able to query remote server tables and join them to local tables. Thought that linked server would be best so that I can use local stored procs for parameters if needed. I have no administrative control over remote server. I have been given select permissions only to one db on remote server. Both servers are sql2000, local machine has spk3.
I have established the remote server in network client utility. I can register the remote server in EM, look at tables. I can run select statements against the remote server in QA when I log in and specify that server. I can reference an odbc data connection with web pages and display rowsets.
Next, I created a linked server under security, using sql (since it's an sql 2000 db) with the same name used above in the client utility and the em. I created a local login with the same name and password as the login used successfully above (Paragraph 2). On the security tab, I've used sa as the local login and entered the remote user and password, using the default 'be made without using a security context'. When I try to view the tables (which I can do through registered) or run this through QA:
SELECT * FROM OPENQUERY(CASCE, 'SELECT person_pk, lastname FROM dbName.dbo.tblName (NOLOCK)'),
I get: sql error 17: sql server does not exist or access denied
If I edit security, wipe out the top settings and specify the security context below using the login information, same thing. Tried messing around with different security contexts, no help or ole db error.
Any help? In addition, any tutorials out there a little more user friendly than the bol? I think I want to use the linked server option and stored procs.
View 14 Replies
View Related
Jul 10, 2013
Problem ms sql server 2012 not using remote index on sql server 2000 linked server with odbc
Let say my servers are:
srvdb and remotesrv
when do select on srvdb:
select * from remotesrv.database.dbo.inventory this select not using index on remoteserver
but if this select is on remoteserver like
select * from database.dbo.inventory, this select use index PK_Inventory (clustered)
View 5 Replies
View Related
Jan 29, 2006
How can I improve performance of my search if I am looking in a table with more than ten million rows with a "like query"?
Does putting an index mean only telling the computer to start in a particular order?
if I index all the coloums does my search get faster ?
how can I decide on an indexing strategy?
View 7 Replies
View Related
Apr 9, 2014
I noticed today a session that was executing a FULL SCAN update as follows:
UPDATE STATISTICS [XXXX].[XXXX].[XXXX] [_WA_Sys_00000009_318D45CA] WITH FULLSCAN
When I checked the sys.dm_exec_query_memory_grants DMV for the session I could see the following values:
requested_memory_kb granted_memory_kb used_memory_kb max_used_memory_kb
145,705,216 145,705,216 139,977,336 139,980,408
When I checked the Properties of the Statistic I can see it is on a varchar(3) field when there are only 3 different values in there - all char(1)
The total size of the data in the table according to the Disk Usage By Top Table Report is 199,680,712KB
So my question is this...
For the UPDATE STATS on this one column with FULL SCAN, does SQL Server read the entire table into the Buffer Pool. If so then if the table had 199,680,712KB of data then why did the session request 145,705,216KB.
Or does SQL Server just read the column and ClusteredIndex Key into the Buffer Pool?
View 1 Replies
View Related
May 10, 2007
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"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
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?
Regards,
Skipper.
View 1 Replies
View Related
Apr 23, 2008
Performance issue.
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.
View 15 Replies
View Related
Oct 25, 2000
Hi Everybody,
Can anyone tell me what is the difference between Remote Server and Linked Servers.
thks in advance,
Srinivasan.
View 1 Replies
View Related
Nov 14, 2007
I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).
However, when I execute a query from a remote machine I get an error (see below for the exact message).
I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?
Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?
Errors:
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".
Environment: SQL 2005, Standard Edition SP2
Thanks in advance for any suggestions.
David Rueter
drueter@assyst.com
View 2 Replies
View Related
Jul 23, 2005
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
View 2 Replies
View Related
Jul 20, 2005
I have two tables:T1 : Key as bigint, Data as char(20) - size: 61M recordsT2 : Key as bigint, Data as char(20) - size: 5M recordsT2 is the smaller, with 5 million records.They both have clustered indexes on Key.I want to do:update T1 set Data = T2.Datafrom T2where T2.Key = T1.KeyThe goal is to match Key values, and only update the data field of T1if they match. SQL server seems to optimize this query fairly well,doing an inner merge join on the Key fields, however, it then does aHash match to get the data fields and this is taking FOREVER. Ittakes something like 40 mins to do the above query, where it seems tome, the data could be updated much more efficiently. I would expectto see just a merge and update, like I would see in the followingquery:update T1 set Data = [someconstantdata]from T2where T2.Key = T1.Key and T2.Data = [someconstantdata]The above works VERY quickly, and if I were to perform the above query5 mil times(assuming that my data is completely unique in T2 and Iwould need to) it would finish very quickly, much sooner than theprevious query. Why won't SQL server just match these up while it ismerging the data and update in one step? Can I make it do this? If Iextracted the data in sorted order into a flat file, I could write aprogram in ten minutes to merge the two tables, and update in onestep, and it would fly through this, but I imagine that SQL server iscapable of doing it, and I am just missing it.Any advice would be GREATLY appreciated!
View 3 Replies
View Related
Jan 4, 2008
I must be doing something wrong.
Code Block
TREE Form
ssql.Append("INSERT INTO FINDINGS (Facility) ")
ssql.Append("VALUES ('" & Facility & "')")
Try
Dim NewRow As Integer = dba.ExecuteSQL_Affected(ssql.ToString)
Catch ex As Exception
MsgBox("There was an error saving records.", MsgBoxStyle.Information, "No Key")
Exit Sub
End Try
Assessment.dtblFindings_Initialize()
Code Block
Public Function ExecuteSQL_Affected(ByVal sSql As String) As Integer
'//Execute the query like Insert, Update and delete
Dim RowsAffected As Integer
Try
If Conn.State = ConnectionState.Closed Then
Conn.ConnectionString = "Data Source=" & oDBConfig.LocalDBLocation & "" & oDBConfig.LocalDBName & ";"
Conn.Open()
End If
Dim cmd As New SqlCeCommand(sSql, Conn)
cmd.CommandType = CommandType.Text
RowsAffected = cmd.ExecuteNonQuery()
cmd.Dispose()
Conn.Close()
Return RowsAffected
Catch err As SqlCeException
MsgBox(Utility.ComposeSqlErrorMessage(err))
Catch ComErr As Exception
MsgBox(ComErr.ToString, MsgBoxStyle.Information)
Finally
End Try
End Function
Code Block
Assessment Form
Public Sub dtblFindings_Initialize()
Dim rdr As SqlCeDataReader
Dim dba As New DBAccess
Dim ssql As StringBuilder = New StringBuilder
ssql.Append("SELECT Facility FROM FINDINGS")
rdr = dba.OpenResultSet(ssql.ToString)
Try
rdr.Read()
While rdr.Read
...
So here is the problem. The normal function is to initiate the insert by pression a button. That should go through all the steps then hit the dtblFindings_Initialize command and rebuild the datatable. However when it happens for the first time (i.e. the first facility going into the database), the SELECT statement always returns nothing.
If I stop the application and Pull the database to the desktop, the row has been inserted. So I feel that I am somehow doing something wrong, not closing something, not initializing something....argh! Please help!!
View 1 Replies
View Related
Aug 22, 2007
The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.
DECLARE @OutParam VARCHAR(5)
EXEC ('{Call getnextpin(1)}') AT ALTTEST
GetNextPin takes a single input parameter.
I need to modify the above code snippet to return the next PIN number into @OutParam.
Any ideas?
Any help appreciated.
Regards,
Paul.
View 1 Replies
View Related