Data Access Very Slow In .net As Compared To VB
Jan 9, 2006
Hi,
I have migrated my app from VB to VB.Net. A 3-tier app with remoting and COM+.
I am experiencing a long wait time of about 3 times higher than what it would take in the VB App.
I am using DataAdapter.FiLL method to fill the datatable.
I have tried.
Using DataReader ( Made the things worse )
Using BeginLoadData and EndLoadData
Creating a Dataset and calling fill with the dataset so that the round trip to the middletier is saved to bring the SQL.
But i feel now that whatever is done. the problem is with the fill method only?
Is there any alternative?
Please suggest. It is one of the most important thing which if not possible may lead to scrapping up idea of upgrading to .Net.
Shri
View 10 Replies
ADVERTISEMENT
Sep 7, 2006
To extract data from an ODBC source, try the following:
Add an ADO.Net Connection Manager.
Edit the Connection Manager editor and select the ODBC Data Provider
Configure the Connection Manager to use your DSN or connection string
Add a Data Flow Task to your package.
Add a Data Reader Source adapter to your data flow
Edit the Data Reader source adapter to use the ADO.Net connection manager that you added.
Edit the Data Reader source to query for the data you wish to extract.
hth
Donald
Using the steps outlined above as described by Donald Farmer in another post on this forum, I have created an SSIS package which retrieves data from Lotus Notes 6.55. The DSN referenced by the ADO.Net Connection Manager connects to Lotus Notes via the NotesSQL ODBC driver 3.02g.
When I execute the dataflow, data is transferred from Lotus Notes, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes. Utilizing the same DSN on the same machine, SQL 2005 SSIS completes the transfer in about 57 minutes.
Is there anything that can be done to improve the performance in SSIS to retrieve data from Lotus Notes via ADO.Net ODBC?
Thanks!
View 3 Replies
View Related
Jun 10, 2008
i was using sql 2000, the database contains 500+ tables, 3000+ sp.
i moved to sql 2005 and found problem on generating script (right click database -> tasks -> generate scripts).
i need to generate the table relations.... it is very very slow compared to sql 2000 which is done in about 30 seconds to few minutes.
i already tried many ways including set options to false which in my thought could speed up a lot...but still very slow.
average generate script time with sql 2005 (sp 2): 70-90 minutes.
average generate script time with sql 2000 (sp 4): 2-3 minutes.
can anyone tell why ? thx in advance
View 9 Replies
View Related
Aug 1, 2007
i was using sql 2000, the database contains 500+ tables, 3000+ sp.
i moved to sql 2005 and found problem on generating script (right click database -> tasks -> generate scripts).
i need to generate the table relations.... it is very very slow compared to sql 2000 which is done in about 30 seconds to few minutes.
i already tried many ways including set options to false which in my thought could speed up a lot...but still very slow.
average generate script time with sql 2005 (sp 2): 70-90 minutes.
average generate script time with sql 2000 (sp 4): 2-3 minutes.
can anyone tell why ? thx in advance.
View 2 Replies
View Related
Jun 26, 2007
Hi,
I have a report in SQL Reporting Services 2005 which calls a stored proc and the report takes a very long time to run and sometimes returns zero records. But when i run the stored proc in query analyzer it takes about 4 seconds!!
I have checked the execution log on the RS using the below sql:
Code Snippet
use ReportServer
Select * from ExecutionLog with (nolock) order by TimeStart DESC
It shows that i have a large amount of time for the dataretrieval (601309ms, about 10mins) and does not return any records most likely because of a query timeout:
TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
601309 2227 3 1 rsSuccess 4916 0
The weird thing is that when i run it in query analyzer, i get about 400 records in 4 seconds !!
I dont understand what RS is doing to take up so much time like this to retrieve data.
The report is very simple - it basically returns the records straight out into a table.
The only thing I somewhat suspected was a parameter data type conflict between RS and SQL, specifically dates. I have a start and end date parameter in the report - i tried specifying this as date and string to see if it made any difference but it didn't.
Any help would be greatly appreciated.
View 19 Replies
View Related
Jul 5, 2006
Hi
Here is the brief to my problemWe had our database on SQL Server 2000 and Windows 2000.This machine
had 2gb of RAM and dual Penitum 3 processors and about 25-30 users were
connected all the time. The size of database is around 2 gb. Even on this setup
rate of data retrival was good, never had any issues. We moved to SQL Server 2005 and Windows 2003. This machines has
2 Pentium Xeon 3.4 processors and 2 stick of KINGSTON 1024 MB 333
MHZ DDR DIMM ECC CL2.5 DUAL RANK X4 INTEL. The rate of data
retrival is awful and its very slow. It using about 1.7 to 1.9 gb of RAM all the
time. Page File usage is about 2.07 gb and Virtual Usage is about 1.7gb.I dont quiet understand why is it so slow to get data. We use bespoke software,
so nothing has changed there. Hardware specification of our server is far more better then the recommended
system requirement for SQL Server 2005.Am i missing something out or i havent set up the SQL Server properly? Any help would really be appreciated.Mits
View 3 Replies
View Related
Jan 4, 2007
Hello,
In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DBMicrosoft OLE DB Provider for SQL Server.
In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.
The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.
Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems?
Thank you for your help!
cdun2
View 32 Replies
View Related
Oct 9, 2015
Field is not listed as text in any of the databases it is a varchar(255) - and that can be changed if that is what causes the issue.
But here is my syntax which produces the error Msg 421, Level 16, State 1, Procedure, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
DECLARE @c NVARCHAR(MAX)
WITH c1 AS (
SELECT [abcd] AS table_name
FROM [intranet].[dbo].[soccerfieldinfo]
where [abcd] IS NOT NULL
), c2 AS (
SELECT Row_Number() OVER (ORDER BY table_name) AS r
[Code] ....
View 3 Replies
View Related
Jan 28, 2002
I am experiencing VERY slow connectivity between client and server SQL 2000. I have checked the network activity and it is low. It also takes Enterprise manager ages to load, and then browsing tables and trees is impossible!
HELP!!
View 1 Replies
View Related
Oct 5, 2004
I have a access database, the data store in another server. This noon, one of our user is runing the access database too slow. Open the database and search the data, etc. It took a long time to come out, Any body has experience on it, why, we had etrust install on each user machine, is that cause this too slow? Thanks in advance.
View 1 Replies
View Related
Feb 22, 2008
Has anyone else exported data to Access? If so, is it a slow process in SQL Server 2005?
I am exporting 3,000 records to an Access database using a view and it has taken 20 minutes. I am using the Export Data wizard. Any tips on how to speed it up would be appreciated.
View 7 Replies
View Related
Sep 17, 2007
I have 25+ WinXP SP2 workstations on a Novell file server (Novell login) and also have a W2K SQL 2000 Server for our database. We are usint Win NT authentication to the SQL Server.
One AND ONLY ONE user is having trouble with this setup. Her workstation is PAINFULLY slow accessing the SQL server through my Delphi programs, through our third party programs, and through ODBC connections.
I thought it was her workstation until I configured a clean workstation for her, and it too had the same problem. I then took a brand new Dell 9200 workstation with 2gb memory, gigabit NIC, etc. and configured it to use our servers. It too had the same slowness connecting to the SQL server. Everything else is fast.
I deleted her entry in the NT domain users table, and re-entered her. No help.
Can anyone shed any light on this?
View 3 Replies
View Related
Aug 3, 2007
Hii,
A bit new to SQL 2k5 but here goes, I recently installed SQL 2k5 on a preety decent box with about 4GB Ram and created a database called PointOfSale. As probably obvious by now, the application that accesses the Database is a PointOfSale application.
The store sells ladies clothing items, of which each is barcoded.
When an item is scanned via the barcode reader into the application, it takes like about 10-15 seconds for the item information to appear. Of course the item information resides on the database.
There are three registers and the same thing happens on all. I am running sp 2 for sql2k5 already.
Any thing I can do that would reduce the 15 sec delay significantly?
Any help at all would be appreciated immensley.
Regards,
Burt.
View 2 Replies
View Related
Nov 15, 2006
I have one aspx page that is very slow loading it is connected to SQL 2005 database. I am looking for any preformance tips out there
View 3 Replies
View Related
Aug 27, 2001
We have several people accessing the SQL server 7.0 data thru' MS Access Server has been slow on queries etc.Everything looks good,other than this one finding of MS Access being used for update/select. Is MS Access a potential problem? How to solve the problem of slow response time?
Note: MS Access is running on the client desktops retrieving data from SQL Server and joining data from MS Access to SQL Server.
View 2 Replies
View Related
Jan 26, 2000
I have a stored procedure which creates 3 temporary tables. Every table is about ten rows and 25 columns. The inserts in the tables goes fast (< 30 ms). The selects from them is also that fast. BUT the first select takes about 3200 ms one each of the temptables. (I first do insert, then select from them.) So the SP executes at about 13 seconds instead of 3.
Any suggestions, anyone, please?
View 1 Replies
View Related
Jun 27, 2006
We have an issue with accessing SQL Server 2000 where the access of data from the database is slow unless the user is logged in as an administrator to their computer.
The system is as follows: SQL Server 2000 on a W2K server. Users logging into a Win 2003 domain server. Users using W2K on their workstations. Application is VB.NET using the Enterprise Library Data Block, connection pooling ON, and windows authentication.
We are assuming that the issue is down to one of authentication and that when a user is set as an administrator then they have instant access. We have been able to replicate the issue using just SQL server on a W2K workstation and accessing from another W2K workstation. Again data access is way slow unless the account is an administrator.
Glad of any ideas folks!
Kind Regards
Ian Logan
View 10 Replies
View Related
Feb 1, 2008
I have a table that has appx 3.2 million rows. see sp_help
Name Owner Type Created_datetime
-------------------------------------------------------------------
TB_SAAI014_BPD dbo user table 2005-08-10 11:33:23.893
Column_name Type Comp Lngth Prec Scale Nullable
------------------------------------------------------------------------
RowID int no 4 10 0 no
SPHInstID int no 4 10 0 no
BPDInstID int no 4 10 0 no
BMUID varchar no 11 no
InfoImblCfw numeric no 9 12 2 no
BMUPrdNonDel numeric no 9 12 2 no
PrdFPN numeric no 9 13 3 no
PrdBMUBalSrvVol numeric no 9 13 3 no
PrdInfoImblVol numeric no 9 13 3 no
PrdExpdMtrVol numeric no 9 13 3 no
BMUMtrVol numeric no 9 13 3 no
PrdBMUNonDelBidVol numeric no 9 13 3 no
PrdBMUNonDelOfrVol numeric no 9 13 3 no
TranLossFctr numeric no 9 15 7 no
TranLossMtpl numeric no 9 15 7 no
TradUnitName varchar no 30 no
TotTrdUnitMtrVol numeric no 9 13 3 no
BMUAppBalSrvVol numeric no 9 13 3 no
DTCreated datetime no 8 yes
DTUpdated datetime no 8 yes
Identity Seed Inc Not Repl
-----------------------------------------
RowID 0 1 0
RowGUIDcol
-----------------------------
No rowguidcol column defined.
Data Located on File Group
==========================
PRIMARY
Index Name Decsription Keys
----------------------------------------------------------------------------
idx_SPH_BPD clustered, unique located on PRIMARY SPHInstID, BPDInstID
This table has 1 clustered index based on its own unique record ID and that of its parent table record
I have an import process that adds appx 980 rows of data to this table and numerous rows to several other tables as part of a transaction and it ran in about 15 seconds.
However we suffered a server failure and it had to be rebuilt (Svr2k3), SQL 2000 re-installed (with default options) and the data base restored.
The same transaction is now taking 8 to 9 minutes.
I tracked it down to this particular table. Just doing a count(*) takes over 5 minutes. Select * where ID = 1 takes over 5 mins. Also, whenever the table is accessed you can hear the server thrashing the disks. Other tables, although smaller do not seem to be suffering from this masive performance drop..
I've tried droping and recreating the index. I have even created a copy of the table, with index, and still get the same issue with speed.
DBCC CHECKTABLE returns the following but takes 6 and a half minutes
DBCC results for 'TB_SAAI014_BPD'.
There are 3168460 rows in 72011 pages for object 'TB_SAAI014_BPD'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No errors are shown
A DBCC CHECKTABLE on another table with 230 thousand rows, run at the same time only took 10 seconds
Can anyone please point me in the direction of things to check, try or repair.
Any help greatfully recieved.
Jinx1966
View 10 Replies
View Related
Feb 10, 2007
I've noticed that after the database have been idle for some time, it takes up to 10 seconds to get it started when something needs to access it. In the event viewer it says that the database <name> have been started.
Obviously, there is some idle timeout setting.
I saw an option in the database properties that is called "Auto Close" which is set to true. I assume this is what i'm looking for. Can someone confirm that? (it could take some time to test myself...)
But what i'm actually wondering is:
1. Is it possible to adjust how long it would wait before timing out?
2. What advantages does closing the database bring? Does it free up (a noticeable amount of) ressources? Or is it only that it's unlocking the files, so that it's possible to copy the database source files?
View 4 Replies
View Related
Dec 19, 2006
I have an Access2000 ADP that I want to run under Access2007. The problem I have is that some forms take up to 45 seconds to open in Access2007! These are not complicated forms--just simple navigable reference forms like setting up transaction types etc. that are based on basic select statements like:
SELECT * FROM ArReceivableType
Where ArReceivableType is a reference table (less than 10 columns, all int or nvarchar(100) max) containing about 15 or 20 rows. They open instantly in Access2000.
I put a trace on to see what is happening on the SQL Server, and I noticed heaps of nasty code like this that generates tens of thousands of reads:
select object_name(sotblfk.id), user_name(sotblfk.uid), object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk, sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and object_name(sofk.id) = N'FK_FaAssetTransactionWork_ArReceivableType_ArReceivableTypeId'
It looks like Access2007 is reading all of the constraints for the underlying table, including all foreign keys. My SQL database contains 1400+ tables all with properly constructed foreign keys and other constraints.
Any suggestion on how to NOT have Access2007 do this? Right now, Access2000 works great for this enterprise app, but I really like the new Access2007 features (and I don't want to still be developing Access2000 apps in 2010).
View 3 Replies
View Related
Jun 6, 2006
Hi,
first time poster/newbie here.
I've
got a football (soccer for the yanks!) predictions league website that
is driven by and Access database. It basically calculates points
scored for a user getting certain predictions correct. This is the URL:
http://www.pool-predictions.co.uk/home/index.asp
There
are two sections of the site however that have almost ground to halt
now that more users have registered throught the season. The players
section and league table section have gone progressively slower to load
throughout the year and almost taking 2 minutes to load.
http://www.pool-predictions.co.uk/home/players.asp?tab=a_d
http://www.pool-predictions.co.uk/home/table.asp
All
the calculations are performed in the Access database Ive written and
there are Access SQL queries to get the data out.
My
question is, is how can I speed the bloody thing up! ! Somone has
alos suggested to me that I use stored procedures and SQL Server to
speed things up? Ive never used SQL Server before so I am bit scared
about using it (Im only a hobbyist), and I dont even know what a SP is
or does. How easy will it be upgrading the whole thing to SQL Server
and will it be worth the hassle, bearing in mind I expect my userbase
to keep growing? Do SP help speed things up significantly? Would
appreciate some advice!
Thanks in advance,
John.
View 1 Replies
View Related
Jun 29, 2007
One of our developers has a Microsoft Access 2000 database that runs queries that compare the Access db data to a SQL Server database. He uses pass through queries to get the data from SQL Server.
We're finding that the Access query runs quickly against our test server, even with copies of production data, but when we try the same query against our production server, the CPU on the local computer running Access is pegged and the query takes up to 10 minutes to run.
First I verified that the SQL Server structures between test and production were identical, including indexes. I checked index fragmentation, and productions indexes are less fragmented than tests. Again, test and production currently have the identical data.
I've run a profiler trace on our production SQL Server 2000 server, and I see the RPC for the query from Access running almost instantaneously.
Any ideas on what might be the cause of the difference in speed between test and production SQL Server servers, or any suggestions on other things I could look at/tools I could use to troubleshoot this issue further?
View 1 Replies
View Related
Nov 15, 2007
I have an application built with Access 2003 (MDB). It is running under Windows XP without any problems. If I run it under Vista, it works technically well but I get the data very slow from the server.
Server:
Windows Server 2003 R2, SP2
SQL Server 2005, version 9.00.3054.00
Firewall: off
Client:
Access 2003, SP3
Connection strings:
ODBC;DRIVER={SQL Native Client};UID=SD_Admin;DATABASE=SDX;SERVER=MARS;PWD=xxx;
or
ODBC;DRIVER={SQL Native Client};DATABASE=SDX;Trusted_Connection=Yes;SERVER=MARS;
Windows Firewall on client: off
Onecare Firewall on client: off
How can i Fix this issue?
View 2 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
Sep 22, 2015
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 4 Replies
View Related
Apr 2, 2004
anyone know reasons why sql server 2000 is a better choose for db creation instead of say oracle and mc access???
THANK YOU
View 10 Replies
View Related
Dec 7, 2007
There is no doubt that SSIS has been designed to build on the lessons learned from DTS but in one respect I think that there may have been a backward step and that is ease of use. A big attraction (a hallmark even) of DTS was that you could configure a package to move data between data sources and destinations in minutes.
When you first start using SSIS this is far from being true. I tended to get a lot of errors (many of them not very helpful) relating to stored procedure parameters, truncation of data etc. Looking around this Forum I think that I am not alone. This is partly a learning curve issue but I think that the learning curve is much steeper for SSIS.
.
This is not a plea to go back to DTS, we must move on, but I would like to think that there will be an emphasis on improving ease of use for SSIS going forward.
For the record two general improvements that I can see in SSIS (over DTS) are:-
1. A more logical structure.
2. The ability to easily set any Package Property, not just Variables at run time.
View 7 Replies
View Related
May 10, 2006
I've written a stored proc which passes in an SqlString parameter and compares it with an SqlString read from an SqlDataReader.
I get the following exception:
System.Data.SqlTypes.SqlTypeException: Two strings to be compared have different collation.
Any ideas on collation inside CLR stored procs?
Thanks.
View 3 Replies
View Related
Aug 11, 1999
I am running an application on one NT Server, running against SQL Server 6.5 sp 3, and SQL 7 with sp1 applied.
The application is a 'data migration' type application - ie heavy insert and update workload - against many (50+ tables) with many different SQL statements.
The SQL 7 server is configured with 'floating' memory.
On SQL 7 - I am experiencing very high page faults/second for the sqlservr process - sometimes peaking at over 1,000. I was under the impression any number greater than 10 indicates a problem with system performance.
The same application, same data, same NT configuration etc against SQL 6.5 does not page fault. SQL Server 6.5 completes the work faster than 7.
Could anyone help me understand what's going on ?
Thanks in advance.
View 2 Replies
View Related
Jul 23, 2005
Working on some new code, I'm coming across WHILE loops used instead ofcursors. I was curious if anyone had any stats on how the speed ofdoing this compares to the speed of a cursor. I typically avoidcursors for performance sake, but I'm not sure how this avoids thespeed hit of a cursor, since it's doing essentially the same thing.Many thanks.
View 4 Replies
View Related
Jun 12, 2007
Hi,
We have Asynchronous Database Mirroring on SQL Server 2005 SP2 Entprise Edition/Windows 2000 Advanced Server. We noticed that log sent rate is quite low (average 1.3 MB/sec) in most of the cases whereas "Log bytes flushed/sec" is high (1.4 MB/sec) as a result Log send queue keeps on increasing and finally taking all the transaction log space. Our disk queue length is always in range of 0.01. And prinicipal and mirror servers are on local LAN.
I tried on low end server and high end server and in both cases Log sent rate is approx 1.3 MB/sec (Maximum 4 MB/sec).
Is there any limitation on Log sent rate?
How can we improve on log sent rate? Since both servers are on local LAN, network bandwith does not seems to be an issue.
Any help is greatly appreciated.
Thanks,
Ramesh
View 2 Replies
View Related
Jun 30, 2015
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.
View 4 Replies
View Related
Jul 20, 2015
HowTo: Import data to MS SQL 2008 from password protected Access DB ?
View 2 Replies
View Related
