|
Is there a way to configure\create a datatable (strongly typed or untyped)
that can have a row accessed in constant time if you are searching on the
primary key?
If there isn't a way, then what is the quickest way to access a row when
using datatables?
Thanks,
Ryan
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Ryan Andrus wrote: have a row accessed in constant time
What is "constant time"?
Additional: To the person that voted this a 1. Why is this not a valid question? When I read the OP's request I wondered if he meant "real time" or something else. So I asked for clarification. What is so wrong with that?!
-- modified at 19:49 Tuesday 26th June, 2007
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
O(1) in Big O notation which basically means that the amount of time to find one row in the datatable is independent of the number of rows. In other words, it will always take the same amount of time to access a row in the datatable. The same way that getting an element in an array in C++ is constant time because it is done through pointer arithmetic and not by iterating through the elements.
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Please have a look at Indexes and Clusters.
I think it will reduce the time from O(n) to O(log n).
Regards,
Arun Kumar.A
|
|
|
|
|
Are you refering to index's on the Database table? That is not what I am asking about. I am talking about System.Data.DataTable. If there is a way to index a System.Data.DataTable can someone please post up a how to?
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
I am asking about System.Data.DataTable not a table in the database. Do System.Data.DataTable's support index's?
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Hi
i have 2 questions in SQL 2005
First:
How to upload a picture in the database as i want each user to put his pic
second:
How to make the search CaseSentive
so it can diffrentiate bet
bahaa & BaHaa
Thanks a lot
|
|
|
|
|
Bahaa Hany wrote: How to upload a picture in the database as i want each user to put his pic
Use a varbinary(max) column to store this. When communicating between the database and your application use parameters. The data must be sent as a byte array (byte[] in C#)
Bahaa Hany wrote: How to make the search CaseSentive
Change the collation sequence to a case sensitive (CS) one. This is one of the options at install and is the best time to make that desision because it is a PITA to change it afterwards. Also, you will have great difficulty making comparisons between columns in different collation sequences so it is best pick one and apply it across the whole server.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Have a little patience. Reposting the same question, "spamming", within 24 hours will likely get you ignored in the future.
|
|
|
|
|
I am building an application similar to a help desk application. This application allows the management to assign and track all assigned tasks. The users will be a create a trouble ticket and track and update assigned tasks.
Below is the current ERD:
DEPT(DEPT_SID, DEPT_NAME)
TASK(TASK_SID, TASK_DESC)
PRIORITY(PRIRITY_SID, PRIORITY_LEVEL)
USERS(USERS_SID, USERNAME, LAST_NAME, FIRST_NAME)
STAFF(STAFF_SID, STAFF_USERNAME)
ASSIGNMENT(A_SID, STAFF_SID, PRIORITY_SID, TASK_SID, DEPT_SID, USERS_SID, A_TITLE, A_DESC, A_DATE)
ASSIGN_LOG(A_LOG_SID, STATUS_SID, A_SID, A_LOG_DATE, A_LOG_NOTES, RESOLVE_DATE)
STATUS(STATUS_SID, STATUS_DESC)
Question:
1) How do I change the design to I only use one table for "Create By:" and "Assign To:"?
Comments and design suggestions are welcome.
Thanks,
Steve C.
kstv@netzero.net
|
|
|
|
|
astv wrote: Below is the current ERD:
That isn't an ERD. It is a bunch of text. I can only assume (because you haven't said) that the first bit of text represents a table name and everything in the brackets represents the columns. Perhaps the bolded text are primary keys (but who, other than you, can really know). Also a completely wilde guess suggests that the underlined columns might be foreign keys).
If you are going to describe the table structure don't assume people will understand your structure unless you tell them what it means.
astv wrote: 1) How do I change the design to I only use one table for "Create By:" and "Assign To:"?
No where in your structure is "Created By:" or "Assign To:". What does this mean?
astv wrote: Comments and design suggestions are welcome.
I can't. I don't understand what you have, so I can't offer suggestions on how to get where you are going.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
My apologies. What I have posted are the tables and fields. I followed how my book on how to describe the tables and fields. I tried to paste an image of my ERD but I could not figure it out.
[Table Name]([PK], [FK], [Other Fields])
The fields "Created By" and "Assigned To" are a part of the ASSIGNMENT table. Again I apologize, I missed this two fields. Since, "Assigned To" can have multiple values, this may have to be normalized. This is the piece that is unclear to me.
Another is, if a task is reassigned to a different user, I may want to know who reassigned this specific task.
Thanks for your response.
Steve C.
kstv@netzero.net
|
|
|
|
|
|
So your assignment table actually looks more like this:
A_SID [PK]
STAFF_SID [FK]
PRIORITY_SID [FK]
TASK_SID [FK]
DEPT_SID [FK]
USERS_SID [FK]
A_TITLE
A_DESC
A_DATE
CreatedBy
AssignedTo
astv wrote: How do I change the design to I only use one table for "Create By:" and "Assign To:"?
You also mention that you want to record if a person reassigns a task. I would guess that should go in the ASSIGN_LOG table. You may want to add a couple of nullable columns for that. You might also want to keep the current assignee in the ASSIGNMENT table for performance reasons.
Also, I don't know what you mean by "only use one table for "Create By:" and "Assign To:". They already are in one table.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Yes the assignment table looks exactly like how you have it.
Ok, if I understand you correctly, when an entry is made in the ASSIGNMENT table, an entry is also made in the ASSIGN_LOG table. This would make sense since my intent for the ASSIGN_LOG table is to keep assignment historicals.
as for The use of single table for "CreatedBy" and "AssignTo" question:
Currently, in my design I use the USERS table to populate the CreatedBy field and the STAFF table to populate the AssignTo field.
USERS
USERS_SID [PK]
USERNAME
LAST_NAME
FIRST_NAME
STAFF
STAFF_SID [PK]
STAFF_USERNAME
ASSIGNMENT
A_SID [PK]
STAFF_SID [FK]
PRIORITY_SID [FK]
TASK_SID [FK]
DEPT_SID [FK]
USERS_SID [FK]
A_TITLE
A_DESC
A_DATE
CreatedBy
AssignedTo
Is this ideal to use two different tables storing basically the same information? Should I change the STAFF table relationship to ASSIGN_LOG table?
STAFF
STAFF_SID [PK]
STAFF_USERNAME
ASSIGN_LOG
A_LOG_SID [PK]
STATUS_SID [FK]
A_SID [FK]
A_LOG_DATE
A_LOG_NOTES
RESOLVE_DATE
Thanks,
Steve C.
kstv@netzero.net
|
|
|
|
|
astv wrote: Is this ideal to use two different tables storing basically the same information? Should I change the STAFF table relationship to ASSIGN_LOG table?
I was guessing that CreatedBy and AssignedTo would be staff ids (or it could be user ids)
Actually, what is the difference between staff and user in this context?
So in Assignment what does USERS_SID represent? What does STAFF_SID represent? And CreatedBy and AssignedTo? (becuase I'm guessing about all of this)
You aren't, as far as I can see, using two tables to store basically the same information. The Log table shows the historical information, the Assignment table shows the current state along with other information. If you are writing queries that are not interested in historical data then you don't want an additional join to the log table because it will slow things down. It would also be quite a complex join because you'd need to join on the most recent only.
Whether you duplicate the current state in the historical log or not is up to you. I would because it means when you are looking for everything upto and including the current it is there.
So, you might want to set up a trigger on Assignment to INSERT a row in the log to ensure that it is always up-to-date. The minor duplication of data isn't a problem if it is properly managed.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
In ASSIGNMENT table, USER is the CreatedBy and STAFF is the AssignedTo information. The SID is to prevent freeform entry. a combo box or boxes will be populated with with usernames and the SID is what is passed to the ASSIGNMENT table. I think I am duplicating entries again. I do not think I need the AssignedTo and CreatedBy fields if I am going to keep the USER and STAFF tables.
Do you think I should move the STAFF relationship to ASSIGN_LOG?
I like the trigger idea to INSERT a row in the log to ensure it is up-to-date.
Thanks,
Steve C.
kstv@netzero.net
|
|
|
|
|
astv wrote: Do you think I should move the STAFF relationship to ASSIGN_LOG?
Copy maybe, but not move. It would be good to log that information, but you'd need the current status available also.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Hey,
I'm using this database through MS Access with my program and I need to be able to use the LIKE keyword. My forms have specific, formatted field names that are sequential and uniform. Essentially, the first part of every form field name is a part of its subsequential heading.
IE.
I have a 'heading' column in my table, 'paragraphs'. One example of my headings is 'Scope of Services & Workplan/Methodology Overview'.
Now, if I were to use a query searching for that exact name everything works fine. However, I need to use the LIKE clause as follows:
SELECT heading FROM paragraphs WHERE heading LIKE 'Scope%'
This is because the first part of my form fields are the first words of every heading. ie. Scope_1_chbx, Scope_2_chbx, and so on.
My LIKE clause is resulting in the query returning no results....can anyone help me? Thanks.
---
reegan41
|
|
|
|
|
Your select statement looks okay to me. Perhaps your data is the problem. I've had problems with the LIKE feature because of selecting LIKE 'ABC%' when my data was in lower case, LIKE 'abc%'.
If that is not the case, could you please explain what you are doing further? What do you mean with the Scope_1_chbx, Scope_2_chbx stuff?
Could you give us some actual data from the tables?
Hope this helps.
|
|
|
|
|
I actually figured it out...believe it or not, it was because I was using LIKE 'data%' instead of LIKE "data*"
For some reason THAT worked instead...odd, but thanks for your help regardless
|
|
|
|
|
You were using an MS Access database? What version?
|
|
|
|
|
I have following error in window service same code is working in window Application
"QueryInterface for interface SQLXMLBULKLOADLib.ISQLXMLBulkLoad failed."
Dim objbl As SQLXMLBulkLoad3
objbl = New SQLXMLBulkLoad3
objbl.ConnectionString = "Provider=SQLOLEDB.1;Data Source=pcDB;Initial Catalog=webdb;User ID=sa;pwd=sa"
objbl.ErrorLogFile = "c:\error.log"
objbl.BulkLoad = True
objbl.KeepIdentity = False
objbl.CheckConstraints = False
objbl.Transaction = False
objbl.Execute("c:\Items.XSD", "C:\Items.xml")
Thanks
Hardik Panchal
|
|
|
|
|
Don't cross post in multiple forums. It's considered very rude to "spam" the forums and makes it quite difficult for multiple people to collaborate on an answer.
|
|
|
|
|
Hi,
I want to improve the following query's performance executing on sqlce.
select *
from TABLE_A
where field_a in (select field_a
from TABLE_B
where field_b = '0000162080'
and field_c = '2'
and field_d = '011')
order by field_e
Table_A has index for field_a and Table_B has index for field_b and field_c. How can I rewrite the query to execute faster?
thanks,
- ferudun -
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
