|
cbhkenshin wrote: but the second option will give fast select and slow insert or delete and also a very large number of rows.
This isn't necessarily correct. It depends on how you are inserting the rows. For instance if they key is sequential (like an IDENTITY or a timestamp) then new rows will be appended to the the end of the table. If the key is on something more random then the rows will be potentially inserted in the middle and other rows around it will be shuffled around. But it will likely only affect one additional data page.
As for deleting, I'd guess it would still be the same speed because the row is only marked as being deleted. It isn't actually removed at the time of delete.
Have you tested your performance hypotheses? Do you know for certain that option 1 has a faster insert than option 2, and vice versa for select?
Personally (and this is without knowing the full story), I'd go for option 2 and then refactor to option 1 if it becomes a problem. Also, you didn't say which is more important, the ability to insert lots of data, or the ability to select lots of data? Which do you do more of? (That will have an influence on your final answer)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi Frenz!
I have a table with 10 Duplicate rows in a SQL Server Database. In that table there is no unique identification column name (eg: SlNo, RowNo... like that) means all rows having a common values and that table does not have any constraints. So I want remove first 9 duplicate rows from that table. How?
Kindly give the suggestions to solve this problem. coz I am learner in making Querys in SQL Server 2000.
wish me all the best.
-- modified at 23:52 Friday 10th February, 2006
|
|
|
|
|
You could try a SELECT with a GROUP BY clause containg every column name in your table, and the output columns having an additional COUNT(*)
For example:
SELECT Column1, Column2, Column3, COUNT(*)
FROM MyTable
GROUP BY Column1, Column2, Column3
ORDER BY DESC COUNT(*)
This will float all the duplicates to the top of the result set. If you have no primary key or unique constraint on any of the columns then you have no way to reliably delete only the duplicates automatically. You will need to take note of the duplicate rows and delete them manually. (Actually, I think there might be, but if you only have 9 duplicates, it would be faster to just do it manually)
I'm curious as to why you don't have a primary key on the table? I've never come across a situation where I'd leave out a primary key.
Does there look like a good candidate for a primary key? It might be a good idea to create one.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi all (again!)
I am new to sql database design, and am in the process of learning it all really quick....Our old POS program was written in a DOS 4G dbms, which is completely different from what I am learning. I have a quesion about database design. I opted to start with a timesheet program that I wrote originally in this other stated ide. I am looking to crate a database for a timesheet. I have thought about this for a while and came up with a few idea's, but would like some input, being new to this and all. I need to store values of the time clocked in and clocked out, in weekly intervals.
Here it goes:
Employee Info Table
Includes employee number, name, password, security level
and then a table for each day of the week,
or a table with a timein and timeout for each week, with of course the employee id,
or create a table at the time the employee first signs in with a timein and time out for each day of the week...each day also needs to be tagged with if the hours are regular work hours, vacation, jury duty, holiday, sick, etc.
Thats basiclly the info I need stored. There is no need for an 'approved by' column, or things of that sort. A pretty basic program that gets the job done.
Any advice/help is greatly appreciated!
Thanx in advance!
|
|
|
|
|
Database design is not an easy topic to just jump into. I could attempt to provide a schema for what you are asking, but it would be a temporary fix at best. Better for you to learn to "fish" as the saying goes. In general, the goal of a database design is to prevent data redundancy and to organize your data into a logical fashion. Much beyond that would require far more than a simple forum post. I would suggest finding yourself a good database design book (I'm afraid I can't suggest one, perhaps another poster will be better able to) and perhaps taking advantage of Google to see what you can see. About.Com Database Design has quite a few interesting looking guides.
After leading off with the above, for your specific problem I would say you're on the right track, an employee table tracking employee specific information. For your timesheet tracking, most decisions would require a more in depth knowledge of the domain (which you have), but to hazard a guess I would say something along the lines of a Timesheet table with EmployeeId, Date, ClockInTime, ClockOutTime, and WorkType. Of course, this all depends on how picky you are about data constraints, but based on what you've said it is what I would suggest. While the idea of separate tables for each day may seem more prudent, you need to think in terms of the data you will have to retrieve. Something as simple as retrieving an employee's total hours for the year would be costly, involving joining eight different tables.
I hope I came across at least reasonably clear, and I wish you the best of luck in learning database design.
|
|
|
|
|
Thanx for the input. Here is the solution I came up with.
Table 1 - Employee Info
Table 2 - Timecard - Employee ID, Time in, Time Out, Date, Work Type
Table 3 - Table Name - Employee ID, Timecard Table name
and for the programming solution, create a table everytime an employee signs in for the first time, and give that table a unique name, store that name in the table name table, along with the Employee ID.
Does that sound feasable?
Thanx again!
Common sense...the least common of the senses used
Jude
|
|
|
|
|
You only need two tables.
1. Employee info : ID, Name , etc.
2. Timecard data: ID, TimeIn, TimeOut, Date, Work Type.
All clock in records for all employees go in the 2nd table.
A new record is added each ime a any employee cloicks in, clocking out updates the timeOut record (actually I would make a dateIn and date out field to allow for clock-out on different day than clock in.
There is no need for a per employee table, since the per employee DATA can be extracted and summarized by queries any time (for example: "select TimeIn ,TimeOut, WorkType from TimeCard where IX = xxx and date between zzz and www")
The goall\ is to store the data in a fashion that minimizes data duplication and data storage size while also minimizing complexity. A timecard table per employee makes no sense to me , and cerainly adds complexity. It would, on the other hand, make sense to have a "work Types" table, with WokTypeId,WorkTypeName as fields, so the id could be the only thing stored in the TimeCard table, thus minimizing data storage required, ad insuring data consistency (you can change the spelling or name used for work type, without breaking existing data, since the WorkID would remain the same). It also provides a quick place to build selection lists of the legal work types for your UI.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Exactly as he said. Based on what you've already said, you would probaly want to extract your Security Level for your employees to another table as well, for the same reasons as the work type.
|
|
|
|
|
Thank a whole lot for your input. I got it together and working.
Very well appreciated!
Common sense...the least common of the senses used
Jude
|
|
|
|
|
I am trying to write a decimal value to a table, but it is only showing the integer part.
The definition for the column is :
Hours decimal 5 scale 3 allow nulls precision 5
What the debugger has is like "9.233", but the table states 9?
Thanx in advance!
The code for storing:
SqlCommand cmdSetHours = new SqlCommand(strUpdate,conn);
cmdSetHours.CommandType = CommandType.StoredProcedure;
cmdSetHours.Parameters.Add("@TotHours",SqlDbType.Decimal).Value = decTotalHours;
cmdSetHours.Parameters.Add("@EmpId",SqlDbType.Int).Value = m_intEmployeeID;
cmdSetHours.ExecuteNonQuery();
the procedure is :
(
@TotHours decimal,
@EmpId int
)
AS
UPDATE Friday
SET Hours = @TotHours
WHERE EmpID = @EmpId
RETURN
|
|
|
|
|
TheJudeDude wrote: I am trying to write a decimal value to a table, but it is only showing the integer part.
What is the type being used for the field? I'd look at the field and make sure it is not an integer.
Paul
|
|
|
|
|
The definition for the column is :
Hours decimal 5 scale 3 allow nulls precision 5
but, Thanx for the quick reply!
|
|
|
|
|
Have you looked at what is going on in the following line?
cmdSetHours.Parameters.Add("@TotHours",SqlDbType.Decimal).Value = decTotalHours;
I tried to replicate your problem from the database side, and couldn't  I would suspect something going on with the setup for the stored procedure.
I hope this helps some,
Paul
|
|
|
|
|
Well, in the debugger, the paramTotHours = #.## ( whatever that value may be)
Thanx again
-- modified at 19:10 Friday 10th February, 2006
Got it...didn't define the parameter correctly, as in
@TotHours decimal (5,2).
Thanks for the help again!
|
|
|
|
|
I need to find a way to change the background color of rows in my DataGridView if the row is marked as modified. Note that this is not the same as changing the background color of a cell when its value changes. The latter can be done easily through the CellValidating and CellValidated events, though this is completely unsatisfactory because the change in color gets lost when you sort a column. But I need the whole row's background color to change.
This seems to imply to me that I need to override the cell classes to override their Paint handlers. The difficulty with this is that in the Paint handler of a cell I can't find a way to get access to the cell's bound DataRow to tell if its RowState is modified. I can only see a way to get hold of the DataRow for the currently selected grid row, not for any row in general. If the grid has been sorted, then you cannot use the cell's grid row to index correctly into the dataset's table.
Any definitive help would be greatly appreciated.
-- modified at 12:12 Friday 10th February, 2006
|
|
|
|
|
can i use a subquery field to my main query.
i.e
select x.field1,y.field2
from tbl1 as x
where x.field3 not in (select y.field3 from tbl2 as y)
|
|
|
|
|
Yes, do be aware that a 'not in' query can be very lengthy if your tables are large.
|
|
|
|
|
thanks,
than what can i do ?
|
|
|
|
|
Sorry - didn't see your reference of y.field2 in the SELECT. Your original query will not work.
You can try a left join:
SELECT
x.field1,
y.field2
FROM
tbl1 x
LEFT JOIN
tbl2 y
ON (x.field3 = y.field3)
WHERE
y.field2 IS NULL
-- modified at 14:37 Friday 10th February, 2006
|
|
|
|
|
thanks, through it i solved my problem.
thanks again.
|
|
|
|
|
try this,may work.
select x.field1,y.field2
from tbl1 as x，tbl2 as y
where x.field3 != y.field3
青山隐隐水迢迢，秋尽江南草未凋。二十四桥明月夜，玉人何处教吹箫。
|
|
|
|
|
The above will work, although you could also just do a normal inner join and use an inequality instead of the usual equality join, instead of the above example with a cartesian product and a where clause.
SELECT x.field1, y.field2
FROM tbl1 as x
INNER JOIN tbl2 as y
ON x.field3 != y.field
|
|
|
|
|
Hi
what is the difference between joins & sub query?
what is the difference between having & compute?
tnx in advance
-ramani
|
|
|
|
|
|
Hi friends,
I have been developed one clien-server application using VB.NET and ADO.NET tech's. I installed my application and MS SQL Server 2000 in server system. but when I am going run the application at Client system it gives the error message as : "Server access denaied" and/or "Unable to connect the server system."
But my application is running perfectly after I installed the Database server at client system without current working datebase.
So I feel that it is required to install the MS SQL server on both server system and client systems. I am not interesting to do in this way.
Kindly, give me the proper solution for problem.
wish me as All The BEST.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
