March 5, 2008 at 6:02 am
Hi guys,
I have a big problem which i am unable to solve. tried many ways but could get it to work. this is the scenario.
I am developin an application on vb6 which will have sql 2005 enterprise edition server as the back end.The client has different branches so he needs to see what ever is happening and need all stored in a branch wise database and headoffice. I am usin access to save the branch data and usin sql 2005 to save all data from all branches.
After developin the app is just connected another computer just to check if its workin. But when running the app on the computer i developed it the app is running fine. But when the app is running on the client/other computer it takes a hell of a lot time. For example when i click a button it takes ages. The client/other computer is new has 512ram, p4 3.0 ghz, vm900 mobo etc i dont think it slow at all. the network is workin fine as well with no packet loss and workin @ 100mbps (so it says when connected).
I just cant figure out what is wrong. first i though may be because it needs to send files to the sql server then i closed sql code. but still even when it supposed be workin with its own C:/test.mdb file for example it takes its own time. I cant think of a way to speed it up.
Can some one please help me. I am stuck and exhausted all my options. tried a variation of connection strings and still with no luck. the access file had a password tuk it out and still the problem exists.
can some one please help me. Thanks a billion in advance pleaaase help me.:crying::crying::crying::crying::crying::crying::crying::crying:
:crying::crying::crying::crying::crying::crying::crying::crying::crying:
:crying::crying::crying::crying::crying::crying::crying::crying::crying:
:crying::crying::crying::crying::crying::crying::crying::crying::crying:
March 5, 2008 at 4:17 pm
You can try running profiler on the SQL Server to see that the app is actually logging in correctly (if at all). And to see how the queries it is submitting are performing.
Failing that, more info please.
- What sort of things is the app doing?
- Is it web-based or executable-on-a-client-pc based?
- Is it all buttons or just particular buttons causing the issue?
- What sort of authentication is being used?
- Does the app authenticate by itself or try using the credentials of the user using it?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
March 5, 2008 at 7:26 pm
Hi scott,
thanks for the reply. I will try running the profiler . The problem exists when the app is workin with access as its back end as well.
There is hardly any data on it. just 4 or 5 rows with 4 or 5 fields.
the app is a simple invoicin system. just select the items from combo box add them with quantity and print the invoice when the save & print button is pressed. the problem exists from loadin the app till it is closed. it takes about 10 - 20 secs for each button or action.
app is not web based. executed at client computer and is installed in all computers.
for the access data base the connection string is as follows.
cn1.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\VB Projects\sudasingha\dpm.mdb;Uid=Admin;Pwd=dpm"
I cannot open the access file which has passwords through jet 4.0 it works fine when jet is used(so i think) but slows down when the driver is defined or used a darasouce through ODBC setting in control panel.
awaiting further more advice
March 12, 2008 at 2:18 pm
Are you actually using Access as the front end? Or just as a database and VB 6 forms as the front end?
If just as the database, have you tried using SQL Express instead?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2008 at 5:21 pm
dileep_ratnayake (3/5/2008)
I cannot open the access file which has passwords through jet 4.0 it works fine when jet is used(so i think) but slows down when the driver is defined or used a darasouce through ODBC setting in control panel.
It has been a long time since I did this, but, You want to use Jet, especially if you are using VB6 to go to a remote Access database. There are a whole bunch of things that Jet is just way smarter about when it comes to MDB's.
Otherwise you will have to profile your VB code (not the server), find those data statements that were fine with jet, but real slow with ODBC and then code around them (much harder).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 15, 2008 at 9:21 pm
im using access as the back end and vb6 forms as the backends.
i think i sorted the slow problem out. but do u think vb6 and sql 2005 may not work together because the applications have 7 years if gap in technology?
March 15, 2008 at 10:48 pm
I used vb6 just fine with Sql2000. I would think that you could get it to work acceptably with Sql2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 16, 2008 at 6:22 am
I'm probably off the mark, but, make sure user actions, such as a button click are not wrapped in an explicit transaction. That would cause intensive blocking and account for the slow-down you are experiencing.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 17, 2008 at 10:51 am
dileep_ratnayake (3/15/2008)
im using access as the back end and vb6 forms as the backends.i think i sorted the slow problem out. but do u think vb6 and sql 2005 may not work together because the applications have 7 years if gap in technology?
This may seem basic, but have you checked to assure that you are using up to date dlls.
Microsoft Active X data objects 2.8 - msadp15 rev 2.81.1128.0 or later
Microsoft Data Binding Collection VB 6.0 (SP4) - Msbind.dll - rev 6.1.97.82 or later
Microsoft OLEDB Service Component 1.0 Type Library - oledb32.dll - rev 2.81.1117.0 or later
Are you using ADO to insert the Access data into the SQL 5K Db and ADO to extract said data from SQL 5K Db for your needs?
I have used VB6 with ADO without experiencing undue delay with SQL 5K
March 17, 2008 at 12:32 pm
I just read Saturdays news letter and it may have an idea for you
http://pragmaticworks.com/community/blogs/brianknight/archive/2008/03/12/ssis-lab-runtime-performance-differences-between-dts-and-ssis.aspx
When you upgrade to SSIS, you can see a dramatic improvement in performance. For example, I took a simple DTS package which pulls one million rows out of a comma-delimited text file and writes that data to a SQL Server table. In DTS the average runtime over five runs of the package was 33.2 seconds for the million records. The same upgraded package took 11.3 seconds on average if you choose to migrate to a SQL Server Destination in the Data Flow task or 12.3 seconds on average for a standard OLE DB Destination. This was on a multi-core laptop with 2 GB of RAM and the package was run 5 times for closer accuracy
Suggests to me that you could export your Access data into csv file(s) and then use SSIS to import.
Any way it is an idea you might want to follow up on.
This is an excellent artice by Brian Knight.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply