Conceptual and speed question

Conceptual and speed question
Hi,
My program seems to slow down drastically because as I fill my array and
table with many values, the program suffers tremendously. The first thing my
program does is to search the jagged array to try to find an element in that
array. If it does not find that element in that array, then it adds another
element and that is the problem. Once I have many elements in that array, it
takes a long time to do a search. Furthermore, not only do I add an element
to that array if it does not find it, but I also add it to my table and as
my table gets bigger, it also slows everything down drastically.
What I thought may be possible is to create many arrays instead of only 1
array and that would speed up the search. The problem is that I do not know
what the size of each array will be at the beginning as some of the arrays
might be much larger than others. Is there a way to declare arrays and then
to dynamically increase their size when needed, and if it is possible would
this hamper the performance because my arrays will be quite large. I also
thought of maybe sorting the array, but that would take too long. Is there a
way to index an array to speed up the search?
As for my table in the database, I was wandering if I created many tables
would that also help speed up the process or will it not make a difference
because it is in the same database. And if that is the case that it does not
make a difference in speed because it is in the same database, then can I
save it to a different database? Another possibility I thought of is to save
the rows to another database and delete the rows in original database after
a certain number of rows have been added.
TIA
Roy"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> Conceptual and speed question
>
> Hi,
>
> My program seems to slow down drastically because as I fill my array and
> table with many values, the program suffers tremendously. The first thing
> my program does is to search the jagged array to try to find an element in
> that array. If it does not find that element in that array, then it adds
> another element and that is the problem. Once I have many elements in that
> array, it takes a long time to do a search. Furthermore, not only do I add
> an element to that array if it does not find it, but I also add it to my
> table and as my table gets bigger, it also slows everything down
> drastically.
>
> What I thought may be possible is to create many arrays instead of only 1
> array and that would speed up the search. The problem is that I do not
> know what the size of each array will be at the beginning as some of the
> arrays might be much larger than others. Is there a way to declare arrays
> and then to dynamically increase their size when needed, and if it is
> possible would this hamper the performance because my arrays will be quite
> large. I also thought of maybe sorting the array, but that would take too
> long. Is there a way to index an array to speed up the search?
>
> As for my table in the database, I was wandering if I created many tables
> would that also help speed up the process or will it not make a difference
> because it is in the same database. And if that is the case that it does
> not make a difference in speed because it is in the same database, then
> can I save it to a different database? Another possibility I thought of is
> to save the rows to another database and delete the rows in original
> database after a certain number of rows have been added.
>
> TIA
> Roy
>
I'll skip the question about arrays. There are no arrays in SQL Server and
you didn't mention what other product you might be using.
On the database side you should first be considering what indexes might
support your application better. Indexes are likely to have much more impact
on performance than splitting the table up.
David Portas
SQL Server MVP
--|||Array, what programming language are you using? This group is for SQL Server
T-SQL.
Loading a large resultset into an application array is very slow, especially
if you are using something like ReDim to increase the size of the array each
time you loop through a client side cursor. Whatever, you are doing with an
array, consider just using an ADO recordset.
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> Conceptual and speed question
>
> Hi,
>
> My program seems to slow down drastically because as I fill my array and
> table with many values, the program suffers tremendously. The first thing
> my program does is to search the jagged array to try to find an element in
> that array. If it does not find that element in that array, then it adds
> another element and that is the problem. Once I have many elements in that
> array, it takes a long time to do a search. Furthermore, not only do I add
> an element to that array if it does not find it, but I also add it to my
> table and as my table gets bigger, it also slows everything down
> drastically.
>
> What I thought may be possible is to create many arrays instead of only 1
> array and that would speed up the search. The problem is that I do not
> know what the size of each array will be at the beginning as some of the
> arrays might be much larger than others. Is there a way to declare arrays
> and then to dynamically increase their size when needed, and if it is
> possible would this hamper the performance because my arrays will be quite
> large. I also thought of maybe sorting the array, but that would take too
> long. Is there a way to index an array to speed up the search?
>
> As for my table in the database, I was wandering if I created many tables
> would that also help speed up the process or will it not make a difference
> because it is in the same database. And if that is the case that it does
> not make a difference in speed because it is in the same database, then
> can I save it to a different database? Another possibility I thought of is
> to save the rows to another database and delete the rows in original
> database after a certain number of rows have been added.
>
> TIA
> Roy
>|||Hi,
Sorry, I am using C# and SQL Server 2005 Express edition.

> On the database side you should first be considering what indexes might
> support your application better. Indexes are likely to have much more
> impact on performance than splitting the table up.
What do you mean by indexes. I am not searching the database, as I am only
adding a row sequentially. The only search that I am doing is in my jagged
array and if the search fails to find the element in the array, then it is
added to the array and then to the table. So you see, I am just using the
table as a permanent repository, the search is conducted in the database.
The problem is that I have millions of rows to fill and as the table gets
larger, it slows down drastically.
Any suggestions.
Thanks
Roy
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> I'll skip the question about arrays. There are no arrays in SQL Server and
> you didn't mention what other product you might be using.
> On the database side you should first be considering what indexes might
> support your application better. Indexes are likely to have much more
> impact on performance than splitting the table up.
> --
> David Portas
> SQL Server MVP
> --
>|||Sorry there is an error, as this:
So you see, I am just using the table as a permanent repository, the search
is conducted in the database.
should be:
So you see, I am just using the table as a permanent repository, the search
is conducted in the array.
Roy
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> Hi,
> Sorry, I am using C# and SQL Server 2005 Express edition.
>
> What do you mean by indexes. I am not searching the database, as I am only
> adding a row sequentially. The only search that I am doing is in my jagged
> array and if the search fails to find the element in the array, then it is
> added to the array and then to the table. So you see, I am just using the
> table as a permanent repository, the search is conducted in the database.
> The problem is that I have millions of rows to fill and as the table gets
> larger, it slows down drastically.
> Any suggestions.
> Thanks
> Roy
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> Hi,
> Sorry, I am using C# and SQL Server 2005 Express edition.
>
> What do you mean by indexes. I am not searching the database, as I am only
> adding a row sequentially. The only search that I am doing is in my jagged
> array and if the search fails to find the element in the array, then it is
> added to the array and then to the table. So you see, I am just using the
> table as a permanent repository, the search is conducted in the database.
> The problem is that I have millions of rows to fill and as the table gets
> larger, it slows down drastically.
> Any suggestions.
> Thanks
> Roy
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
>
If you really don't know what indexes are then you have a LOT to learn. I
suggest you purchase a book or take a course in that case. Newsgroups are
not the place for a tutorial.
How do you explain: "I am not searching the database, as I am only adding a
row sequentially" followed by: "the search is conducted in the database"? So
are you searching or not? Common sense says that if you are generating
millions of rows then you will want to search them at some point. For that
indexes will be essential. However, generating large quantities of data on a
desktop spec machine (if that is in fact what you are doing with SQL
Express) is likely to result in a lot of RAM paging to disk. SQL Server
works best with lots of RAM to itself.
David Portas
SQL Server MVP
--|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:phsff.23046$AZ3.243820@.wagner.videotron.net...
> Sorry there is an error, as this:
> So you see, I am just using the table as a permanent repository, the
> search is conducted in the database.
> should be:
> So you see, I am just using the table as a permanent repository, the
> search is conducted in the array.
> Roy
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
>
You mean you persist the entire set of data in the array AND in the
database? That's going to be extremely inefficient. On a single box both
will compete for resources. Search in the DATABASE - that's what it's for.
If you just want a bit bucket then write to a file not to SQL Server.
David Portas
SQL Server MVP
--|||Hi,
If you saw my next post I said that I made an error and that it should have
been the search is conducted in the array and not database.
I will take your advice and try adding some more memory, as I think that may
be one of the problems.
Thanks
Roy

> If you really don't know what indexes are then you have a LOT to learn. I
> suggest you purchase a book or take a course in that case. Newsgroups are
> not the place for a tutorial.
> How do you explain: "I am not searching the database, as I am only adding
> a row sequentially" followed by: "the search is conducted in the
> database"? So are you searching or not? Common sense says that if you are
> generating millions of rows then you will want to search them at some
> point. For that indexes will be essential. However, generating large
> quantities of data on a desktop spec machine (if that is in fact what you
> are doing with SQL Express) is likely to result in a lot of RAM paging to
> disk. SQL Server works best with lots of RAM to itself.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:EdidnYWlGfGpzuPeRVnyrg@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> If you really don't know what indexes are then you have a LOT to learn. I
> suggest you purchase a book or take a course in that case. Newsgroups are
> not the place for a tutorial.
> How do you explain: "I am not searching the database, as I am only adding
> a row sequentially" followed by: "the search is conducted in the
> database"? So are you searching or not? Common sense says that if you are
> generating millions of rows then you will want to search them at some
> point. For that indexes will be essential. However, generating large
> quantities of data on a desktop spec machine (if that is in fact what you
> are doing with SQL Express) is likely to result in a lot of RAM paging to
> disk. SQL Server works best with lots of RAM to itself.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi,
I only search the value in the array first. If the value is not found, then
I added to the array and then to the table. So you see I need the search the
array to determine whether I have to add it to the table or not.
I thought that it would be much slower if I searched the database rather
than the array, correct me if I am wrong. Furthermore, you say that if I
only want to save it then I should use a normal text file. Would that make
it faster than saving it to a database.
Thanks
Roy

> You mean you persist the entire set of data in the array AND in the
> database? That's going to be extremely inefficient. On a single box both
> will compete for resources. Search in the DATABASE - that's what it's for.
> If you just want a bit bucket then write to a file not to SQL Server.
> --
> David Portas
> SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:D-2dnUa4HoOdyePeRVnyuA@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:phsff.23046$AZ3.243820@.wagner.videotron.net...
> You mean you persist the entire set of data in the array AND in the
> database? That's going to be extremely inefficient. On a single box both
> will compete for resources. Search in the DATABASE - that's what it's for.
> If you just want a bit bucket then write to a file not to SQL Server.
> --
> David Portas
> SQL Server MVP
> --
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:gDsff.23050$AZ3.249690@.wagner.videotron.net...
> Hi,
> I only search the value in the array first. If the value is not found,
> then I added to the array and then to the table. So you see I need the
> search the array to determine whether I have to add it to the table or
> not.
> I thought that it would be much slower if I searched the database rather
> than the array, correct me if I am wrong. Furthermore, you say that if I
> only want to save it then I should use a normal text file. Would that make
> it faster than saving it to a database.
> Thanks
> Roy
>
>
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:D-2dnUa4HoOdyePeRVnyuA@.giganews.com...
>

> I only search the value in the array first. If the value is not found,
> then I added to the array and then to the table. So you see I need the
> search the array to determine whether I have to add it to the table or
> not.
>
You can do that in one operation if you use the database:
INSERT INTO your_table (x, y, ...)
SELECT ?, ?, ...
WHERE NOT EXISTS
(SELECT *
FROM your_table
WHERE x = ?
AND y = ?) ;
If x and y are unique and indexed then this is very efficient.

> I thought that it would be much slower if I searched the database rather
> than the array, correct me if I am wrong. Furthermore, you say that if I
> only want to save it then I should use a normal text file. Would that make
> it faster than saving it to a database.
SQL Server is an extremely efficient way to search and process large
quantities of data. What is inefficient is to keep retrieving the data for
processing outside the database and then to persist large data sets outside
the database as well. Why can't you implement whatever your array is doing
in the database itself? Of course I can't be certain that it will be more
efficient that way but I am pretty certain that there must be a more
efficient method than what you are describing.
David Portas
SQL Server MVP
--