|
If the tables are indexed, then as your table grows, the insert (and sometimes the edit) operations tend to slow down - this is because the database needs to manage the indexes during the insert. However, a million rows (on a properly indexed table) should not be a problem for a select statement. The referse holds true on a non-indexed table - inserts tend to remain fast but select operations slow down.
There are a couple of design patterns that are applicable. One is to create multiple tables, one per month, one per year, whatever is necessary. This keeps the indivual table size small. You would then create a VIEW to represents a union of all your tables so your select statements always pull from a single entity. Another design pattern is to keep all of your data in a single, indexed, table but have a separate, non indexed, table that acts as an insert queue. New data gets inserted into the queue and that data is moved into the permenant table on a nightly basis (inerted into permenant table, delete from queue). You would still need a VIEW to provided a union between your queue and the permenant table, but you don't need to update the view as new tables are added.
|
|
|
|
|
Thanks a lot for your input. I think I prefer your second option (using a queue), although I have no idea what a VIEW is. I am pretty newbie in DB. What is the quickest/simplest way to copy data from one table (queue) to another (master)? Do I need to use stored procedure (which I don't know yet)?
Since my program will be run by multi-users in a 24/7 warehouse, I cannot do nightly transfer. So this i what I think: have an idle counter, if it hits (let's say) one hour, then that workstation start transferring one entry at a time (checking if it's still idle after each transfer). Of course I need a "setting" table in database indicating that "transferring in process" so that any other 1-hour-idle workstations won't try doing the same thing.
Will this work? Thanks again for any help.
|
|
|
|
|
This isn't a trivial process. Though simple on the surface, there is a lot you need to think about. First, transfering one entry at a time will hurt performance. You would be better off ignoring the queuing concept entirely and just inserting directly to your main table. The general logic you want to follow is this:
Start the transaction
insert from queue into master
delete from queue
commit transaction
syntax for inserting from one table to another is:
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1
Use a lock hint to lock your tables until the transaction is completed, so for example you might have the following code:
BEGIN TRANSACTION<br />
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM WITH (TABLOCKX)<br />
DELETE FROM TABLE2<br />
COMMIT
For performance purposes, you may also want to drop the indexes in your main table prior to the insert and then recreate them after the insert.
|
|
|
|
|
Won't it take a long time to re-create index after deleting it?
After re-thinking about this, I started to wonder: how beneficial is it to use a temp queue? How slow is it to insert an entry to one-million-entry table? Do you have any approximate number?
If it's still acceptable (maybe less than 10 seconds), than I'll just drop this queue idea. I will still create one table per year, though.
Thanks for all your help.
|
|
|
|
|
Typically, anything greater than a few milliseconds is considered poor performance. You can easily achieve sub-10 second performance on an insert in a million entry table - assuming that your server is appropriately scaled for the job. For a million record table, you should still be able to achieve sub-second inserts. Make sure you do the math on your performance analysis. For example, if you shoot for 10 seconds in your insert time:
10 seconds * 5000 inserts/day
= 50000 seconds/day spent inserting
= 13.8 hours/day spent inserting records
Create your table, inject a few million records into the table and then do some performance analysis. If the performance is acceptable, then you have your answer!
|
|
|
|
|
I have to gather some info from and database - now the issue comes into play with either coding somethng or writing a extremely difficult query - what to do???
Here is the scenario:
Data from a single table.
Have to calculate columns(sum) according to to certain criteria and have a total at the bottom. Also have to calculate columns(sum) that subtract from a main column and then sum that column. I am just confused as to which: 1) is eaiser ( as the sytem does not have many users) 2) more efficient 3) which would you guys do (code or sql query)? 4) Is a query even possible?
Any suggestions or comments in the right direction would be greatly appreciated.
Thank You.
|
|
|
|
|
A Stored Procedure should do the trick nicely (and more efficient, as you don't need to bring all those records on the client side except the sums).
In your SP, calculate the sum of the main column, calculate the sum of the side columns, and then return the sums that you need through output parameters of the SP.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
I would create stored procs to perform the required calculations anbd return the results. SQL server is pretty efficient at calculating aggregates. In any case, this is bound to be more efficient than returning all the raw data over the network, then calculating the results. No matter how much faster your calculation is than SQL servers, it will never make up for the overhead of marshalling all that raw data over the network pipe.
|
|
|
|
|
Only 2 rows have actual data and 4 of them are just either yes (nor) no then calculate from those fileds depending and yes or no across 4 fields and only one field can be yes. Then you sum the row and that column.
Sorry to be pest - i am not very good a stored p.
|
|
|
|
|
Use Excel.
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
|
|
|
|
|
Hi,
I am trying to create a process that will take data source that has been output from a proprietary ISAM database to import into a SQL database. The thing is I want the process to be able to create the required tables based on data structure information that has been somehow encoded into the data source.
Currently the solution I am going with is to spit out a CSV file that has a header with table and data format information followed by rows of actual data that gets parsed by a SQL script however I am sure that Microsoft must have some kind of preferred solution to this kind of problem but I have not been able to find it. I have looked at the the tools that are available when creating a SQL Server DTS package as well as what seems to be available using the new Integration Services but nothing seems to be any better than the solution I just mentioned.
Anyone have any ideas, I am willing to bet there is a much better way of doing this.
|
|
|
|
|
Can the ISAM database be accessed via, say, OleDB or ODBC?
If it can, then a simple Export/Import package should do the trick nicely, shouldn't it?
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
Unfortunately the ISAM database cannot be accessed in this way.
|
|
|
|
|
I am getting a "Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
On My Sql Statement adn I can't figure out why, I have included my statement below, can anybody give me any pointers?
Declare @xmlDoc as varchar(1000)
DECLARE @DocHandle int
Declare @Loc_ORDER_REF varchar(50)
declare @loc_Order_Name varchar(50)
SET @Loc_ORDER_REF = 'Bollox'
declare @DateFormat varchar(10)
-- Get the Current Dateformat stored in l8_config
Select @DateFormat = 'dmy'--(select config_value from l8_config where config_name = 'SQLDATEFORMAT')
set dateformat @DateFormat
Set @loc_Order_Name = null
set @xmlDoc = '<Root><OPType>1</OPType><Purpose>45</ Purpose><Category>7</Category><IssueDate>23/10/2006</IssueDate><StartDate>23/10/2006</StartDate><ReviewDate>23/10/2006</ReviewDate><Brief>dzscdsxdfvszdf</Brief><IHC>1</IHC><SpecEvent>1</SpecEvent><Oedfin>6523</Oedfin><Oicfin>6523</Oicfin></Root>'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlDoc
INSERT INTO OP_ORDER
(
[OP_ORDER_REF],
[OP_ORDER_NAME],
[OP_PURPOSE_ID],
[OP_CAT_ID],
[OP_ISSUE_DATE],
[OP_START_DATE],
[OP_REVIEW_DATE],
[OP_IHC_COPY],
[OP_SPEC_EVENTID],
[OP_OFF_ENT_DET_FIN],
[OP_OFF_IN_CHRG_FIN],
[OP_BRIEF_DESC],
[OP_DIV_FLAG]
)
SELECT
@Loc_ORDER_REF,@loc_Order_Name ,
Purpose,Category,
IssueDate , StartDate ,
ReviewDate ,
Case ltrim(rtrim(IHC))
when 'true' then 1
when 'false' then 0
else 0
end ,
case ltrim(rtrim( SpecEvent))
when 'true' then 1
when 'false' then 0
else 0 End , Oedfin, Oicfin, Brief, OPType
FROM OPENXML(@DocHandle, '/Root',2) WITH (Purpose int, Category int , IssueDate datetime , StartDate datetime ,
ReviewDate datetime , IHC bit, SpecEvent bit , Oedfin varchar(50), Oicfin varchar(50), Brief varchar(1000), OPType Bit )
EXEC sp_xml_removedocument @DocHandle
Select @@Identity
-- modified at 18:00 Monday 23rd October, 2006
"a fool will not learn from a wise man, but a wise man will learn from a fool"
"It is hard to fail, but it is worse never to have tried to succeed." - Theodore Roosevelt
"Success is going from failure to failure without losing your enthusiasm." - Winston Churchill
My Website || My Blogpx" targer="_blank">My Blog</a></div>
|
|
|
|
|
i have a query that returns 2 rows and i want to add 1 row manualy, so the final query would return 3 rows. can anyone help me with this?
cellardoor
|
|
|
|
|
Select your 2 rows into a view or temporary table, then add your additional row into that view/table.
Final step: select everything from that table/view.
Note: if you are using stored procedures, then temporary table will be
deleted automatically when the stored procedure is done (at least in SQL 2005)
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
select Column1,Column2 from MyTable
union
select 9000 as Column1,'whatever' as Column2
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
|
Hi,
In my report for the columns iam providing sorting feature.In the report header i have one image.
Image disappears when using sortable columns.That image originally appears when opening report, but disappears after sorting columns.
Why this happens.
Please help me.
Thanks in advance
|
|
|
|
|
i want to search for specific row in database:
first: stored procedure
create procedure search
@name varchar (50)
as
select * from members where name = @name
second:C#
-create connection and open it
-create command and set it to stored procedure
-create parameter and pass value i want to search for to that parameter
i want to know the index of that row sotred procedure returned to pass it to :
DataRow dataRow = dataTable.Rows[index]
to fill textboxs of other fields "phone,hire_date,email,..."
textbox.text = dataRow["phone"];
|
|
|
|
|
If your Stored Procedure only returns one result (because the field 'name' is unique) then the index would be 0, which is the first row in the DataSet/DataTable that you filled using the Stored Procedure.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
hi all
i need to get the difference between 2 dates
so i use datediff function in SQL to get it but it doesn't work
eveery time it get te same output even if i change the dates i don't know where is the problem
select datediff(year,12/2/2000,getdate())
and the output is 106
tank
abdelhameed81
|
|
|
|
|
abdelhameed81 wrote: select datediff(year,12/2/2000,getdate())
Try putting the date inside apostrophes. Also, it is generally advisable to use the ISO format (yyyy-mm-dd) so that internationalisation problems don't arise.
e.g.
datediff(year, '2000-02-12', getdate())
|
|
|
|
|
Just to further explain what happend. SQL Server interpreted 12/2/2000 as 12 divided by 2 divided by 2000 which is 0.003. Since the the calendar in SQL Server starts on 1st Janurary 1900 you had actually requested the difference in years between 04:19:20 on 1st January 1900 and midnight on 12th February 2006 (or 2nd December 2006 if you use the American format) which is 106.
|
|
|
|
|
Hi
I want to find out the no of rows returned by a select statement in a stored procedure within the stored procedure
ie i have a select statement say
select * from employees where deptid=2
now for the no of rows returned i want to perform a calculation for each row in the stored procedure itself
how can i loop in it and get the no of rows and the records for each row to handle all these in the stored procedure rather than in the front end
Waiting for reply
Happy coding
Subhash
Subhash Chatterji
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
