|
What comes to my mind is doing 2 querys.
The first one with COUNT statement, so you'll know how many records will the query have.
The second, the query that'll return the records.
Paging ?
If your application is Web, ASP.NET DataGrid provides Paging functions. I've used them and they worked fine, but my query never returned more than 200 records.
Free your mind...
|
|
|
|
|
thanks guillermo
but i think that executing two query may add extra overload at the server
what do you see?
|
|
|
|
|
If the server performs the query on behalf of the client, it can check first to see how many records were returned in the query (using various means) and then decide if it should return the entire result set to the client. This also gives you central administration of such a threshold.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Yes there are 2 querys, but both you can make them both work on the same procedure, so it'll be so fast.
As both querys will reference the same table and use the same condition, when the second query executes, it'll be on the DB cache.
Free your mind...
|
|
|
|
|
|
I'm working on an ASP.NET prjoect for work that would allow for Labor tracking (ie % of time spent on a project in a given month) this system will probably be in place for a while and I'm worried that my table may end up getting to big. Fox eample it's basiclly tracking what percentage for what project so my table looks like...
EID - Eployee ID(Links to employee table)
ProjectID - ProjectID (Links to project table)
TimeOn - Percentage on that progect for the month
Month - The Month
Year - The Year
So we have about 100 employees currently and the could be working on any amount of project in a month but lets say 5 is the average so thats about 60 record per employee per year. So total company wide is 6000 per year. Is that too many? There will be times when every single record needs to be read if that makes a difference. Should I be storing each year in a seperate table? Thats seems not very dynamic cause I have to make sure there are tables for the next 10 years or whatever. But anyway please let me know what you think.
Thanks!
|
|
|
|
|
Assuming you are using SQL Server 2000 and EID and Project ID are "int" types and TimeOn, Month and Year are "tiny" types that is 11 bytes per record. Which is around 650 to 700 records per page or 5200 to 5600 records per extent (I cannot remember if it pads to 12 bytes to make each record an even length). Since a whole extent is read when getting data from the database your 6000 records per employee could be read in 2 extents, if EID and ProjectID make a composite primary key with EID the first part. Of course during normal database operations the database is likely to get fragmented but there are ways to reduce this if performance is absolutely critical.
So, in short, no I don't think that is too many records.
--Colin Mackay--
EuroCPian Spring 2004 Get Together[^]
|
|
|
|
|
i install MSDE in my computer but i tryed to add server from visul studio or add connection to database but i cant it prombet me some "server name cannot be found"
|
|
|
|
|
How to Set Data to TextBox when We fetch data in Dataset,
i want to Show Data from Dataset to TextBox
here is Current Code :
On Form Load
sCon.ConnectionString = sConString
sCon.Open()
Dim DaCustomer As OleDbDataAdapter
Dim DsCustomer As New DataSet()
Dim SQL As String
SQL = "Select * from customers"
DaCustomer = New OleDbDataAdapter(SQL, sCon)
DaCustomer.Fill(DsCustomer)
and my TextBox's Name is TextBox1
|
|
|
|
|
TextBox1.Text = DsCustomer.Tables["customers"].Rows[0]["COLUMN_NAME_HERE"].ToString()
And that's about it.
------------------
I'm naked under my clothes...
|
|
|
|
|
Fellow Geeks:
I am a new VB.NET programmer and am trying to open a MS Access 2000 database using the following code:
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; DataSource= C:\My Documents\Access 2000\RightPath.mdb"<br />
Dim mySelectQuery As String = "SELECT * FROM ANNUALSPECS"<br />
Dim myConnection As New OleDbConnection(strConn)<br />
Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)<br />
myConnection.Open()
When I run the .Open(), I get the error: "Could not find installable ISAM".
I have been able to find a little in the documentation about this, but it has not been much practicle help. Has anyone else seen this, and what can I do about it?
Jeff
|
|
|
|
|
Hi Jeff,
That error usually means a connection string typo. Try putting a space between "Data" and "Source".
Datagrid Girl
|
|
|
|
|
 Well, that was only slightly painful and embarassing! Thanks for the additional eyes in finding this!
Jeff
|
|
|
|
|
I have a table with only one field of type string.
This table is loaded into a list control and the user is allowed to manipulate its data by adding/removing elements on it.
When the user is ready he has the option to save this list back to the database.
Effectively I have two lists, list A which exists on the database and list B which exists in memory.
List B is mofified by the user and must be saved back to List A in the database.
In your opinions, what is the best method to use when saving list B?
1. Delete all records from A and dump B into A
2. Iterate B, checking for each item's existence in A. If item does not exist, add to A. Iterate A and check for each item's existence in B. If item does not exist in B, delete it from A
3. Like (2) but other way around. Iterate A checking for each items existence in B. If item does not exist, delete it from A. Iterate B and check for each item's existence in A if item does not exist, add it to A
There could be around 10,000 records in the list.
List A and list B do not necessarily have to exist on the same computer - they might be connected over the LAN.
Jeremy Pullicino
C++ Developer
Homepage
|
|
|
|
|
my company run their project mgmt stuff on an access db. i've made various changes to this app however its extremely slow, especially on our network when more than one person wants to use it. i've since moved this to asp which everyone is happy with, despite the loss of some features. I've taken the initiative and am moving it to MySQL (boss won't cough up for ms sql) as the database is growing, new features, reporting, etc needs to be added. i've also considered moving the web app to .net, however i don't see any real benefit to the end user (just more work for myself) and its not solving the underlying issues. And one of the main bug-bears is the printing of reports -> the reports just don't cut it when printing from a web page especially if it is a multi-page report. I've come to the conclusing that i should be rewritting this app as a win32 application using C#. my biggest question is should i use a client/server configuration or just have the client application connect to the database and do whatever. the database is on a winxp machine which also serves as our file server and web server (intranet). We have approx 10 users on our network. is there a real need to go the client/server route or will the simple client app do the trick. the database is on a separate machine within our local network however the client machines can still see it and connect to it. and help, advice and/ideas are greatly appreciated.
|
|
|
|
|
How do you create the reports?
modified on Monday, August 30, 2010 6:38 AM
|
|
|
|
|
well, now we are still using the access db for printing multi-page reports (simply because we get the headers, footers, page number, etc automatically and nicely formatted). for single page reports we use an activex component that can print web pages without all the crap that normal prints with it (url, etc) but have to rely on html formatting.
|
|
|
|
|
|
thanks i'll have a look at that.
|
|
|
|
|
I want to return a larg set of records from database server
to the client tier -win forms-
so the question
1-what is the effective way to do this?
2- is it differ if they connected via Wan or via the Internet
thanks in advace
|
|
|
|
|
Two very basic advices:
1. return ONLY the data that you really need (do not use SELECT * ...),
2. return all data using ONE batch to minimize the network traffic; don't do something like:
do
[your query]
while (...)
modified on Monday, August 30, 2010 6:42 AM
|
|
|
|
|
thanks chopeen for your reply
of course i will return the data that i realy need
so the question at the case that i really need many records
when you work with disconected data you may have situation like this
you want to create dataset which have many records or if you implement your business objects
so the question again ho to do this effectively
thanks again
i need your advices gurus
|
|
|
|
|
You can use a DataSet object. It works perfect on disconected enviroments. Also look information on the DataAdapter object.
The DataAdapter, will translate the modifications done to your DataSet to the DataBase.
Free your mind...
|
|
|
|
|
Thanks Guillermo Rivero
it seems that i did not explain what i need exactly
i know that i can use dataset on disconected enviroments but Is it will work fine if
the returned records = 1000000 ? for example.
in situation i need this data
|
|
|
|
|
Well, I once did load a DataSet with 2 million records and it worked fine.
Each record had 5 fields.
Free your mind...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
