|
INSERT is costly if there's a lot of indexes. It is not that much about resizing, there's usually a lot of free spare space in a DB and hardly anything needs resizing.
If you can come up with a better strategy than indexes, I'd love to hear it.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
|
mike7411 wrote: time
... define ...
|
|
|
|
|
I was looking at the documentation for CREATE INDEX in SQL:
https://www.w3schools.com/sql/sql_create_index.asp
CREATE INDEX index_name
ON table_name (column1, column2, ...);
I don't understand when you would create it for multiple columns. Let's say you did this:
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
Does that create one index on two columns? Or, is that the same as creating two indexes?
For instance, does that statement create two hashtables? Or one hashtable?
Thank you.
|
|
|
|
|
|
One index. First column is most significant, second is then used to reduce it further when doing searches.
I don't think you should consider it as a 'hash table'. At a minimum I doubt any relational database uses a hash, in general, to fully implement an index. I suspect hash tables are not an optimal solution for file based storage. A quick google suggests SQL Server specifically supports that type but for a memory table (so not a normal table.)
I don't want to do the deep dive research to actually figure out what they might use for db indexes. But you can read up on what Clustered Indexes are.
Although not directly related the one example I know without research is the comparison of doing sorting. Binary Sort is for memory. Heap Sort is for file based processing.
|
|
|
|
|
Copy/paste from the answer to the question that was posted before yours;
Quote: That means the index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2.
Meaning, in a list of users, while random stored in the table, would have an index that orders it as such;
Lastname, Firstname
Doe, Jane
Doe, John
Vluggen, Albert
Vluggen, Eddy
Vluggen, Zack
If you search it, you go "where lastname = bla (col1 from index) AND firstname = bla2 (col2 from index)".
This way, it will first locate the segment for "Vluggen", and only has to scan until it finds "Eddy" within that segment. That's what the DB is optimized to do.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Let's say you have a SQL database with a table called USERS.
Let's say it has 500,000 users in it.
Let's say you do this:
SELECT PASSWORD FROM USERS WHERE USER='bill.gates';
Let's say there's no indices on the table.
Does the database just do a linear search thru each user?
Thank you.
|
|
|
|
|
Let's say there are books available on SQL, and let's say there are search engines on the internet.
|
|
|
|
|
Yes. It will scan segments and it breaks once all predicates are met. That's why having a PK with a clustered index is so very nice to have. Who comes up with the idea of a non-indexed table? 
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
In data base talk it is known as a 'table scan'.
Most databases have a way which allows you to see what it is doing. Oracle, SQL Server and MySQL IDEs all have a way to see this clearly. The nomenclature used in the output is hard to read but practice makes that easier.
|
|
|
|
|
|
See the question above; you have an index with two columns. That means the index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2.
So, yes, you search those linear. For a table without index, that means scanning the entire table as there is no guaranteed order to the rows.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
"index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2."
(My quote button still not working.)
Re-read that response after looking at post to other response.
That phrasing is a misstatement about how it works.
A table storage in a database is quite complicated. Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster.
There are some implementation details about how that works technically but 'at the end' is the best technical description. Clustered indexes do this differently but not specifically in terms of sorting anything.
After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself.
Sorting is not something that is related to the table nor even to indexes. In SQL you can specifically ask for a specific sort order. If you do not do that then how the records (rows) are returned is somewhat random. And that becomes even more true when things like joins are factored in.
|
|
|
|
|
jschell wrote: That phrasing is a misstatement about how it works.
Please, explain. Please do? Pretty please?
Do explain how it works?
jschell wrote: A table storage in a database is quite complicated
Nope, and quite well documented.
jschell wrote: Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster.
Tables are without any inherent order, by design and definition. Indexes aren't in the table, they are entities outside the table that may be updated.
jschell wrote: After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself.
Hence, the "create index" expects some columns to sort on.
jschell wrote: Sorting is not something that is related to the table nor even to indexes
If you need not sort, you need no index. So yes, it might be a tad related. The definition in proper schooling talks more about lookups than sorting, as that is more common.
jschell wrote: In SQL you can specifically ask for a specific sort order
Thanks for explaining that, that's really something new.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
modified 10-Feb-24 19:17pm.
|
|
|
|
|
I have a window based application that creates new database every month programmatically and in that database i have single table which consists of 50 columns which also . But the problem is after crating database programmatically it falls in recovery mode. So my question is Why database falls in recovery mode.
|
|
|
|
|
Your question is very generalized and can have any of many reasons why it crashes, without proper info (remember that we cannot see your screen to guess the cause) - some causes might be Incomplete Transactions, Corrupted Log Files and so much more...
Without specific details about your database creation process and the exact error messages you are encountering, it's challenging to provide a precise solution.
|
|
|
|
|
This design sound like a disaster waiting to happen - oh wait it has happened.
What possible reason would you create a new database for every months data?
I'd wager that the 50 column table is not normalised, leading to a myriad of problems down the track.
As to why the database falls into recovery mode, the Great Ghu only knows but given the above issues it is the least of your problems.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
As has already been pointed out to you, that design is a bad idea. See SQL Server: The Problems of Having Thousands of Databases on a Single Instance[^]
It's bad for lots of other reasons too. Say you wanted to produce some MI for an entire year's worth of data for your application. You would have to query 12 separate databases ending up with something like
SELECT Col1, Col2 FROM DB1.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB2.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB3.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB5.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB6.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB7.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB8.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB9.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB10.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB11.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB12.dbo.MyTable; And did you spot my deliberate mistake?
Even if you have a new table in the same database each month it doesn't really get any better...
SELECT Col1, Col2 FROM MyTable01
UNION ALL
SELECT Col1, Col2 FROM MyTable02
UNION ALL
SELECT Col1, Col2 FROM MyTable03
UNION ALL
SELECT Col1, Col2 FROM MyTable04
UNION ALL
SELECT Col1, Col2 FROM MyTable06
UNION ALL
SELECT Col1, Col2 FROM MyTable08
UNION ALL
SELECT Col1, Col2 FROM MyTable09
UNION ALL
SELECT Col1, Col2 FROM MyTable10
UNION ALL
SELECT Col1, Col2 FROM MyTable11
UNION ALL
SELECT Col1, Col2 FROM MyTable12; But introduce a Date column into your table and suddenly your code becomes so much clearer
SELECT Col1, Col2, YEAR(MyDate), MONTH(MyDate) FROM MyTable; Next steps are to look at normalizing your single table - see Database normalization description - Microsoft 365 Apps | Microsoft Learn[^] for some starters.
Address these sort of problems first, to take some of the heavy lifting off your SQL Server instance and you might very well cure what ever it is that is going wrong for you.
Failing that you are going to have to examine the Windows Application log to find out more - View the Windows application log (Windows) - SQL Server | Microsoft Learn[^]
|
|
|
|
|
Nilesh M. Prajapati wrote: creates new database every month...Why database falls in recovery mode.
My guess is that it would be due to how you 'create' it.
Certainly if you start with a clean install of SQL Server (just the normal install databases) and if you run you app and it impacts that database, then yes it is specifically how you are creating it.
|
|
|
|
|
The SQLite documentation says that SQLite is thread safe for reading but not for writing. Specifically, they say you can have multiple threads reading from the DB, but only one thread should write at one time.
Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing?
IOW, does reading need to be serialized as well?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
It's "rereads" that are (usually) the problem. If one is writing, and another reading, one for one makes little difference; if the record aren't related. On the other hand, if you're not writing "complete" records at one time, then they have to be "locked".
For "rereads" you have to check for "dirty" (e.g. timestamp changes). "Screen data has been updated".
With related records, you may need need to lock a table or the entire DB (for a very short time).
All calls should be async to avoid UI waits.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Well that seems related to the issue that I'm encountering. Occasionally I'll receive a "Access denied - Database locked" error message, and I can't understand it because all of my writes are synchronized around a single mutex. So I thought maybe I can't read either when a thread is writing to the DB.
It's an INSERT statement that's receiving the Database locked message, so the only other thing I can think of is that there's something wrong with my threading code and it's actually attempting two writes at once.
The difficult we do right away...
...the impossible takes slightly longer.
modified 10-Nov-23 21:46pm.
|
|
|
|
|
An insert implies an index update; or a "split" somewhere. A simple append might be different.
In effect, the "internal" state becomes dirty. Because it is "lite".
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Richard Andrew x64 wrote: Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing?
Yes.
SQLite CVSTrac[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
