Loading large amount of data with unacceptable time
-
I need to show the user all numbers of shares he has within his organization. He will use such shares numbers in viewing a report for people who has for example shares lies between share number 20 and share number 350, he will need to know who are the people owns those shares. I hope my idea become more clearer :)
You haven't said anything that makes me think you REALLY need a master list of every single share, and who owns them. You can query the DB for all the deeds within a range, and then create a list from there in a relatively simple fashion. Good luck with the problem!
My website :: My book revealing the forgotten astronomy of our ancestors.
-
I'm working on an application for a joint-stock company. A joint-stock company is a business entity which is owned by shareholders. Each shareholder owns the portion of the company in proportion to his or her ownership of the company's shares (certificates of ownership). This allows for the unequal ownership of a business with some shareholders owning a larger proportion of a company than others. Shareholders are able to transfer their shares to others without any effects to the continued existence of the company. Shares are collected in a form of a deed. A deed has classes like a deed of 50, a deed of 40 and so on. 50 and 40 means that a deed consists of 50 shares, 40 shares and so on. A joint-stock company may have a 1000 shares that are distributed in the form of deeds. each deed has a start share number and an end share number for example lets take a deed of 50 shares so its shares will start from share no. 1 to share no. 50. Within the database of the company there is a table to store the information of each deed DEED (DEED_NUMBER, START_SHARE, END_SHARE) if we use the previous example of deed of 50 and lets say that the deed number is 100. So the data will become 100 for DEED_NUMBER, 1 for START_SHARE, and 50 for END_SHARE. What I need is to get all the numbers between 1 and 50 and this will be applied to all the deeds. So if I had a 1000 shares distributed in deeds I need to get the numbers 1, 2, 3, 4, .... 1000. I already used a cursor to make such solution through looping on all deeds one at a time getting its start share number and its end share number and get all the numbers between them. However, this solution taking too much time and with a company like the one I'm working on having more than 100,000 to 150,000 deeds the time my solution takes to retrieve shares numbers is unbearable and unacceptable. Thanks for any help and I'm sorry for long post I just wanted to explain what is the mean of joint-stock company so people know what I want.
Assuming you have 3 tables DEED, SHARE, OWNER (although if it is badly designed there may not be an owner table). Select (put in the fields from the 3 table you want to display and a count(*)) from Deed inner join Share on Deed.deedno = share.deedno inner join Owner on Owner.OwnerNo = Share.OwnerNo where ? group by (put in the display fields)
Never underestimate the power of human stupidity RAH
-
Assuming you have 3 tables DEED, SHARE, OWNER (although if it is badly designed there may not be an owner table). Select (put in the fields from the 3 table you want to display and a count(*)) from Deed inner join Share on Deed.deedno = share.deedno inner join Owner on Owner.OwnerNo = Share.OwnerNo where ? group by (put in the display fields)
Never underestimate the power of human stupidity RAH
There is no table for shares. I have only a table for deeds and this table consists of 4 columns (DEED_ID 'identity', DEED_NUMBER, START_SHARE, END_SHARE). For example if we have a deed consists of a 50 shares the values in these columns will be (1, 100, 1, 50) for DEED_ID, DEED_NUMBER, START_SHARE and END_SHARE respectively. What I need is to get all shares numbers, more clearer, if we apply on the above example of a deed of 50 shares starting for share number 1 to share number 50 I need to get the values 1, 2, 3, 4, 5 ..... 50. I need to do that for all deeds I have within my database. So if I have 150,000 deeds each of which consists of 50 shares. then I have 150,000 * 50 = 7500000 shares. I need to get values starting from 1 up to 7500000. I already make a solution by using a cursor for all deeds, getting each deed separately and then getting its start and end share numbers then making a while loop from the start share number to the end share number of that deed, then get the next deed and so on. However, this solution taking a considerable amount of time to load the the shares values and this time is unacceptable.
-
There is no table for shares. I have only a table for deeds and this table consists of 4 columns (DEED_ID 'identity', DEED_NUMBER, START_SHARE, END_SHARE). For example if we have a deed consists of a 50 shares the values in these columns will be (1, 100, 1, 50) for DEED_ID, DEED_NUMBER, START_SHARE and END_SHARE respectively. What I need is to get all shares numbers, more clearer, if we apply on the above example of a deed of 50 shares starting for share number 1 to share number 50 I need to get the values 1, 2, 3, 4, 5 ..... 50. I need to do that for all deeds I have within my database. So if I have 150,000 deeds each of which consists of 50 shares. then I have 150,000 * 50 = 7500000 shares. I need to get values starting from 1 up to 7500000. I already make a solution by using a cursor for all deeds, getting each deed separately and then getting its start and end share numbers then making a while loop from the start share number to the end share number of that deed, then get the next deed and so on. However, this solution taking a considerable amount of time to load the the shares values and this time is unacceptable.
So it is a badly designed table, damn. As David asked why do you need the individual records, what are you going to do with 7.5m records now that you have them!
Never underestimate the power of human stupidity RAH
-
I'm working on an application for a joint-stock company. A joint-stock company is a business entity which is owned by shareholders. Each shareholder owns the portion of the company in proportion to his or her ownership of the company's shares (certificates of ownership). This allows for the unequal ownership of a business with some shareholders owning a larger proportion of a company than others. Shareholders are able to transfer their shares to others without any effects to the continued existence of the company. Shares are collected in a form of a deed. A deed has classes like a deed of 50, a deed of 40 and so on. 50 and 40 means that a deed consists of 50 shares, 40 shares and so on. A joint-stock company may have a 1000 shares that are distributed in the form of deeds. each deed has a start share number and an end share number for example lets take a deed of 50 shares so its shares will start from share no. 1 to share no. 50. Within the database of the company there is a table to store the information of each deed DEED (DEED_NUMBER, START_SHARE, END_SHARE) if we use the previous example of deed of 50 and lets say that the deed number is 100. So the data will become 100 for DEED_NUMBER, 1 for START_SHARE, and 50 for END_SHARE. What I need is to get all the numbers between 1 and 50 and this will be applied to all the deeds. So if I had a 1000 shares distributed in deeds I need to get the numbers 1, 2, 3, 4, .... 1000. I already used a cursor to make such solution through looping on all deeds one at a time getting its start share number and its end share number and get all the numbers between them. However, this solution taking too much time and with a company like the one I'm working on having more than 100,000 to 150,000 deeds the time my solution takes to retrieve shares numbers is unbearable and unacceptable. Thanks for any help and I'm sorry for long post I just wanted to explain what is the mean of joint-stock company so people know what I want.
How are the tables defined? That might help us defining a solution. there are some assumptions we could make like is the share id unique? are they sequential? etc... However let's get a few things straight. - Looping a resultset to check for results is not a very good idea and will be slow. - Returning a million records let's say will be slow, no matter what. Even if your database could build the resultset in milliseconds it still needs to send that data through a wire somehow. (that's why they invented paging ;) ) IOW if you can improve the queries, you might still be in trouble and in need of an alternate solution.
V.
(MQOTD rules and previous solutions) -
I'm working on an application for a joint-stock company. A joint-stock company is a business entity which is owned by shareholders. Each shareholder owns the portion of the company in proportion to his or her ownership of the company's shares (certificates of ownership). This allows for the unequal ownership of a business with some shareholders owning a larger proportion of a company than others. Shareholders are able to transfer their shares to others without any effects to the continued existence of the company. Shares are collected in a form of a deed. A deed has classes like a deed of 50, a deed of 40 and so on. 50 and 40 means that a deed consists of 50 shares, 40 shares and so on. A joint-stock company may have a 1000 shares that are distributed in the form of deeds. each deed has a start share number and an end share number for example lets take a deed of 50 shares so its shares will start from share no. 1 to share no. 50. Within the database of the company there is a table to store the information of each deed DEED (DEED_NUMBER, START_SHARE, END_SHARE) if we use the previous example of deed of 50 and lets say that the deed number is 100. So the data will become 100 for DEED_NUMBER, 1 for START_SHARE, and 50 for END_SHARE. What I need is to get all the numbers between 1 and 50 and this will be applied to all the deeds. So if I had a 1000 shares distributed in deeds I need to get the numbers 1, 2, 3, 4, .... 1000. I already used a cursor to make such solution through looping on all deeds one at a time getting its start share number and its end share number and get all the numbers between them. However, this solution taking too much time and with a company like the one I'm working on having more than 100,000 to 150,000 deeds the time my solution takes to retrieve shares numbers is unbearable and unacceptable. Thanks for any help and I'm sorry for long post I just wanted to explain what is the mean of joint-stock company so people know what I want.
Amr Muhammed wrote:
more than 100,000 to 150,000 deeds the time my solution takes to retrieve shares numbers
So you are going to be returning at a minimum 100,000 rows. And more than likely you are going to be returning more like 10 million rows. So exactly what is going to be using those 10 million rows or even 100,000? Certainly won't be a human.
-
Amr Muhammed wrote:
more than 100,000 to 150,000 deeds the time my solution takes to retrieve shares numbers
So you are going to be returning at a minimum 100,000 rows. And more than likely you are going to be returning more like 10 million rows. So exactly what is going to be using those 10 million rows or even 100,000? Certainly won't be a human.
That is what the client want and I'm at the client service :(
-
How are the tables defined? That might help us defining a solution. there are some assumptions we could make like is the share id unique? are they sequential? etc... However let's get a few things straight. - Looping a resultset to check for results is not a very good idea and will be slow. - Returning a million records let's say will be slow, no matter what. Even if your database could build the resultset in milliseconds it still needs to send that data through a wire somehow. (that's why they invented paging ;) ) IOW if you can improve the queries, you might still be in trouble and in need of an alternate solution.
V.
(MQOTD rules and previous solutions)There is no table for shares. There is only a table for deeds and this table structure or what is important to us in this table are 3 columns Deed_NUMBER, START_SHARE and END_SHARE respectively. DEED_NUMBER including the number of the deed, START_SHARE including the value of the start share number within a deed and END_SHARE including the value of the end share number within a deed. Let's say that we have a deed containing 50 shares and that deed number is 100. So the values in the 3 columns will be 100 for DEED_NUMBER, 1 for START_SHARE, and 50 for END_SHARE. I hope this explanation will help
-
That is what the client want and I'm at the client service :(
I hear that line all the time, accepting it is just plain lazy, you are not doing your client a service unless you investigate the requirement and see if you can meet that requirement closer to their needs. They almost never understand their own needs from a data/IT perspective, that is your job to interpret their need and supply it. Delivering 7.5m records is almost certainly not doing that.
Never underestimate the power of human stupidity RAH
-
So it is a badly designed table, damn. As David asked why do you need the individual records, what are you going to do with 7.5m records now that you have them!
Never underestimate the power of human stupidity RAH
The client needs to see all the shares he has within a DataGridView, then he can select from these DataGridView two share numbers one as a start share number and another as end share number to get all shareholders data who own shares within the range of the share numbers selected from the DataGridView. Lets say that client select 20 as from share number and 5000 as to share number, so the client now needs to see shareholders information who own shares within the range 20 to 5000. I hope you understand what I want
-
I hear that line all the time, accepting it is just plain lazy, you are not doing your client a service unless you investigate the requirement and see if you can meet that requirement closer to their needs. They almost never understand their own needs from a data/IT perspective, that is your job to interpret their need and supply it. Delivering 7.5m records is almost certainly not doing that.
Never underestimate the power of human stupidity RAH
The client needs to see all the shares he has within a DataGridView, then he can select from these DataGridView two share numbers one as a start share number and another as end share number to get all shareholders data who own shares within the range of the share numbers selected from the DataGridView. Lets say that client select 20 as from share number and 5000 as to share number, so the client now needs to see shareholders information who own shares within the range 20 to 5000. I hope you understand what I want
-
The client needs to see all the shares he has within a DataGridView, then he can select from these DataGridView two share numbers one as a start share number and another as end share number to get all shareholders data who own shares within the range of the share numbers selected from the DataGridView. Lets say that client select 20 as from share number and 5000 as to share number, so the client now needs to see shareholders information who own shares within the range 20 to 5000. I hope you understand what I want
In other words, you need to learn to interpret the requirements, just like Mycroft said. There is no need to give them numbers. Just a list of names. If they want numbers, you can simply supply a range, or a total, or both: Person A: 40-90, 200-250 Total: 100 shares. That most certainly doesn't require you to create an intermediary list of all of the numbers; you already have all the required information. The only thing you haven't really addressed in your discussion is whether people can sell parts of their deed? If that is the case you will have a lot more work to get the logic right, and from where you are you will probably have to add a 'SPLIT_DEED' table, or something like that, with the appropriate information.
My website :: My book revealing the forgotten astronomy of our ancestors.
-
The client needs to see all the shares he has within a DataGridView, then he can select from these DataGridView two share numbers one as a start share number and another as end share number to get all shareholders data who own shares within the range of the share numbers selected from the DataGridView. Lets say that client select 20 as from share number and 5000 as to share number, so the client now needs to see shareholders information who own shares within the range 20 to 5000. I hope you understand what I want
Amr Muhammed wrote:
I hope you understand what I want
Please do the math...You present the user with 10 million rows. They spend 0.1 second looking at each row. How long will it take them to get to the last row? This should demonstrate to you and even to the user that there must be some other solution. Usually that solution is that user does in fact know what they are looking for in that 10 million rows. And if you asked for that and then used that information then you could give the user 1000 rows, or even 10 rows.
-
There is no table for shares. There is only a table for deeds and this table structure or what is important to us in this table are 3 columns Deed_NUMBER, START_SHARE and END_SHARE respectively. DEED_NUMBER including the number of the deed, START_SHARE including the value of the start share number within a deed and END_SHARE including the value of the end share number within a deed. Let's say that we have a deed containing 50 shares and that deed number is 100. So the values in the 3 columns will be 100 for DEED_NUMBER, 1 for START_SHARE, and 50 for END_SHARE. I hope this explanation will help
so basically you have this? DEED_NR START_SHARE END_SHARE 1 1 50 2 1 25 3 1 75 If you need get them out the result should be 1 - 150. If that´s the case you could use the SUM function:
Select sum(END_SHARE) from DEEDS
if your START_SHARE = END_SHARE+1 (1-49, 50-74, 75-154 eg) of the previous record you just need to select that last record of the deed containing the shares.
Select END_SHARE from DEEDS HAVING DEED_NR = max(DEED_NR) --(not sure if a having works without a group by, but there are other constructs doing similar things.)
hope this gives you an idea.
V.
(MQOTD rules and previous solutions)