PDA

View Full Version : Is there a limit to sampdb(SQLite) rows ?


Ahmad45123
23/04/2015, 08:10 PM
Hello,

If have like more than 1000 rows in a SQLite db and I'd like to load it in a_sampdb to be added to an array.

So if I did this "SELECT * from `DB`", Will only the first X be loaded or it will be completely loaded ?

And if I did this, Will it be better or selecting all will be better:

for(new i; i < 1000; i++)
{
new DBResult:result = db_queryex(dbHandle, "SELECT * FROM `DB` where `id` = %d", i);
//Proccess the line.
}


Thanks in advanced and will REP.

Vince
23/04/2015, 08:16 PM
A thousand rows is nothing. Even SQLite should easily handle a couple hundred thousand rows. But what you're doing is just a terrible way to do it. Send one query with Limit 1000 instead of 1000 separate queries. Then use a while loop to process the result set (because there may be less than 1000 rows).

Sergei
23/04/2015, 08:19 PM
Query is SELECT * FROM table not DB. As Vince said, one query not thousand. And I hope that you know that you are supposed to free result after you don't need it anymore.

Ahmad45123
23/04/2015, 08:19 PM
A thousand rows is nothing. Even SQLite should easily handle a couple hundred thousand rows. But what you're doing is just a terrible way to do it. Send one query with Limit 1000 instead of 1000 separate queries. Then use a while loop to process the result set (because there may be less than 1000 rows).

Thanks alot... I got my answer :). [I REPed you some time ago, So sorry can't again :P]

what you're doing is just a terrible way to do it
I didn't even put it in my script, I was asking if it will be better in performance or not..
I thought loading 1000 items will be alot.

Query is SELECT * FROM table not DB. As Vince said, one query not thousand. And I hope that you know that you are supposed to free result after you don't need it anymore.
It was just a rough example while I was writing the topic... :P
And yes I know.

PowerPC603
23/04/2015, 09:05 PM
Compare it to real-life.
What would you rather do faster and better, when your wife asks you to go buy 100 beer cans for a party for example?

Go one time to the store and buy all 100 cans at once? (compares to 1 query to get 100 rows)
Or go 100 times to the store to buy 1 can at a time? (compares to 100 queries to get 1 row at a time)

The same goes for your queries.
Sending 1 query to fetch 1000 rows is alot faster than sending 1000 queries to fetch 1 row each time.
A query needs to search your entire database to find the row you need, read it and send it back to your script.
1 query = 1 search.
1000 queries = 1000 searches.

1000 rows is nothing for a database.
That's why it's called a DATAbase.
It is designed to handle huge amounts of data.
MySQL allows 4 gigabyte of data to be returned in one result-set to the program that sent the query.
That's 1 query to fetch 4Gb of data at once.

1000 rows is maybe 100 kilobytes of data, so it's almost nothing compared to what databases can handle.

And yes, "SELECT * FROM table" returns your entire table (even if it has 1 million rows), unless you specify a limit like "SELECT * FROM table LIMIT 1000".