Thanks Armando.
10,000,000 is what I expect but I could have 200,000,000.
Field types - 13 are varchars(50) and 2 are int(10).
I only have one index on each table which is the unique auto-generated id.
Although this is not the most commonly searched field.
Example queries:
Many of them are like this:
ArrayMySRef = Split(mySRef,",")
ArrayMySur = Split(mySur,",")
ArrayMyDra = Split(myDra,",")
ArrayMyPre = Split(myPre,",")
ArrayMySor = Split(mySor,",")
ArrayMyAmo = Split(myAmo,",")
ArrayMyBlk = Split(myBlk,",")
ArrayMyLoc = Split(myLoc,",")
ArrayMyItm = Split(myItm,",")
IF myType = "2" OR myType = "3" AND myCon <> "" THEN
myMax = UBound(ArrayMySRef)
FOR i = 0 TO myMax
sql99 = "UPDATE tblMulti SET CID1 = '"&myID1&"', CID2 = '"&myID2&"', Rep =
'"&myRep&"', SRef = '"&TRIM(ArrayMySRef(I))&"', "&_
"Sur = '"&TRIM(ArrayMySur(I))&"', Dra = '"&TRIM(ArrayMyDra(I))&"', Pre =
'"&ArrayMyPre(I)&"', Sor = '"&TRIM(ArrayMySor(I))&"', "&_
"Amo = '"&TRIM(ArrayMyAmo(I))&"', Con = '"&TRIM(ArrayMyCon(I))&"', FLX =
'"&TRIM(ArrayMyFLX(I))&"' WHERE SMID = '"&ArrayMyIDX(I)&"'"
Con.Execute sql99
NEXT
END IF
The reason being is that the page has a form with many rows and many colums.
When the form is sent, I have to SPLIT the variables - I think this is what
takes the time to be honest.
Thanks again for your help.
James Mackie
Appiam Ltd
Developer
www.appiam.com
james@stripped
07884 494 333
----- Original Message -----
From: "Armando" <dijital@stripped>
To: <win32@stripped>
Sent: Wednesday, December 01, 2004 2:34 AM
Subject: Re: Large DB Problem.
> I'll have to agree with John on this one.. Even with 10 million records,
> your database will not bad _that_ big if you aren't using columns that are
> text type and storing large amounts of text within them. I have a few
> friends who are developing a mysql solution for one of their customers and
> are performing tests based on databases with 50 million records in the
> busiest table.
>
> John is right though.. the key definitely is optimization. Your database
> and your queries as well. If you have access to a development box, perform
> your testing on there and optimize at will without having to mess with the
> production server. If your customer is that serious about the integrity of
> their data and server performance, they should be willing to assist you in
> this. You just have to make sure they understand the impact of not being
> proactive. Remember.. many non-technical people have misconceptions that
> just because you are a technical person, you automatically have all the
> answers, which we all know is not the case at all for any of us :-)
>
> As John suggests, please provide some more information regarding your
> current database structure, particularly the busy table you're talking
> about, and also provide some examples of the queries you are doing. Here's
> some things you can provide to help us help you:
>
> 1) What kind of field types are in your table?
> 2) What indexes do you have in place currently?
> 3) What are some examples of your queries?
>
> Also, one thing I's recommend, just to note it, is try to stay away from
> blobs if you can help it. They are generally more hassle than they are
> worth, and will significantly increase your database size and definitely
> slow performance. Cheers.
>
> Armando
>
> James - Developer wrote:
>
>> I am just testing it over an ADSL connection. My client has between
>> 2MB-5MB leased lines in each branch and we have 10MBps guaranteed at our
>> data centre.
>>
>> The reason I'm testing with ADSL is because most of the web users will be
>> using ADSL because it will not only be internal staff.
>>
>>
>> James Mackie
>> Appiam Ltd
>> Developer
>> www.appiam.com
>> james@stripped
>> 07884 494 333
>>
>> ----- Original Message ----- From: "John Paul Ashenfelter"
>> <john.paul.ashenfelter@stripped>
>> To: "James - Developer" <james@stripped>
>> Cc: <win32@stripped>
>> Sent: Tuesday, November 30, 2004 10:06 PM
>> Subject: Re: Large DB Problem.
>>
>>
>>> I think it's *far* more likely you need to spend some time on database
>>> optimzation. 10m rows is a lot, but not that big -- there are folks
>>> using MySQL for with databases over a *terabyte*. You are going to run
>>> into issues as individual tables get above 2 terabytes, but I'm
>>> guessing you're probably only in the 10-100GB range for the whole
>>> database.
>>>
>>> That said, you might look at MERGE tables -- that way you can
>>> partiotion the table by company, for example, but still have access to
>>> the the whole thing through a merge table.
>>>
>>> Of course all this is speculation since the email is pretty vague :)
>>> So are there particularly slow queries? And which version of
>>> everything? And how are you connecting? etc, etc.
>>>
>>>
>>> On Tue, 30 Nov 2004 21:56:39 -0000, James - Developer
> <james@stripped>
>>> wrote:
>>>
>>>> Hi, wondering if anyone could please help.
>>>>
>>>> I have been writing an ASP/MySQL application over the last 12 months.
>>>> In the last month the company who have paid me have brought 8 other
>>>> companies which do the same as what they do. So, as you can imagine I
>>>> am concerned. I have a main database with around 20 tables. The
>>>> busiest table / the one with potential to become the largest contains
>>>> around 15 columns. I have calculated that within 6 months, this table
>>>> could quite easily have 10,000,000 (10m) records or worse case
>>>> 200,000,000 (200m) records. So I have run a test and carried out a
>>>> bulk insert of 5,000,000 (5m) records and as you can imagine, accessing
>>>> this over the web is very/too slow to be practical.
>>>>
>>>> So, I need to split the database. My initial thought was to split the
>>>> database by adding an extra table for every new client who joins and it
>>>> is predicted to be 1,000 clients per year which is the same as the
>>>> previous years. I then thought this would be the same as my initial
>>>> method because MySQL has to first access one of the 1,000 tables and
>>>> then one of the thousands of records which would not make any
>>>> difference.
>>>>
>>>> My final thought and this is my question: I have decided to add a
>>>> completely new database for every client. This is not a problem
>>>> because the data does not need to be relational between clients. This
>>>> means having 1,000 databases/folders and I will use a DSN string with
>>>> the 'database=;' value to equal the relevant client/database name. My
>>>> conception is that the OS (Windows) will access any one of the 1,000
>>>> databases quicker than MySQL can and then MySQL will find the details
>>>> from the relevant database which will only be 1,000th the size of my
>>>> initial idea.....
>>>>
>>>> Does anybody know if this is correct?
>>>>
>>>> I certainly can't have one large table because the database will be too
>>>> slow.
>>>>
>>>> Thanks very much in advance.
>>>>
>>>> James Mackie
>>>> Appiam Ltd
>>>> Developer
>>>> www.appiam.com
>>>> james@stripped
>>>>
>>>>
>>>
>>>
>>> --
>>> John Paul Ashenfelter
>>> CTO/Transitionpoint
>>>
>>> --
>>> MySQL Windows Mailing List
>>> For list archives: http://lists.mysql.com/win32
>>> To unsubscribe: http://lists.mysql.com/win32?unsub=1
>>>
>>
>>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=1
>
