Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
I discover that when i recounting more then 200 QT on one sheet with this
simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and "unstoppable" (closing frames, but not unload process) Can you give me any advice? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
Don't do it?
Tim "Gregory" wrote in message ... I discover that when i recounting more then 200 QT on one sheet with this simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and "unstoppable" (closing frames, but not unload process) Can you give me any advice? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
Is this advice? )))
I so prefer to store my algorithm (very flexible finance planning sheets with my special formulas) which is very transportable Of cause, i can transfer data to server (with RPC or other technology), make recount, transfer back, but why i must do that? "Tim Williams" <saxifrax@pacbell*dot*net сообщил/сообщила в новостях следующее: ... Don't do it? Tim "Gregory" wrote in message ... I discover that when i recounting more then 200 QT on one sheet with this simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and "unstoppable" (closing frames, but not unload process) Can you give me any advice? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
Hi
Am I right that you are running 200 separate queries with source table(s) in same workbook on same sheet? What do you want to achieve with this, I wonder! Am I right in my suspect, that you calculate some summary values from tables on other worksheets this way? Anyway, I'm sure there are less resource-consuming solutions available - maybe you describe, what kind of original data you have, and what do you want to get out of them. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Gregory" wrote in message ... Is this advice? ))) I so prefer to store my algorithm (very flexible finance planning sheets with my special formulas) which is very transportable Of cause, i can transfer data to server (with RPC or other technology), make recount, transfer back, but why i must do that? "Tim Williams" <saxifrax@pacbell*dot*net сообщил/сообщила в новостях следующее: ... Don't do it? Tim "Gregory" wrote in message ... I discover that when i recounting more then 200 QT on one sheet with this simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and "unstoppable" (closing frames, but not unload process) Can you give me any advice? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
Yes, you are right. I have some arrays of input data (foundries and
registries), which were received from other program sources over 16 orgs in our holding, then finance director preparing finance plan or other reports, dynamically changing input data and report forms (for example, hi describes [2,3,7,8][7-9][2^10-90|2^1000-1143,2^1148-3120,2^3135-5999] as profit of [2,3,7,8] orgs in 3-d quarter, and else) . Of cause, some times, he is making his work w/o internet, that means local realization of algorithm. If i don't resolve time-memory problem by excel ODBC, i should try another ODBC-complain local data-source, may be dbf or mysqld. Almost of all i prefer current realization, cause it's work normal (one time :)) "Arvi Laanemets" сообщил/сообщила в новостях следующее: ... Hi Am I right that you are running 200 separate queries with source table(s) in same workbook on same sheet? What do you want to achieve with this, I wonder! Am I right in my suspect, that you calculate some summary values from tables on other worksheets this way? Anyway, I'm sure there are less resource-consuming solutions available - maybe you describe, what kind of original data you have, and what do you want to get out of them. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Gregory" wrote in message ... Is this advice? ))) I so prefer to store my algorithm (very flexible finance planning sheets with my special formulas) which is very transportable Of cause, i can transfer data to server (with RPC or other technology), make recount, transfer back, but why i must do that? "Tim Williams" <saxifrax@pacbell*dot*net сообщил/сообщила в новостях следующее: ... Don't do it? Tim "Gregory" wrote in message ... I discover that when i recounting more then 200 QT on one sheet with this simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and "unstoppable" (closing frames, but not unload process) Can you give me any advice? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
Hi
I think an ODBC query is a wrong tool here. I myself use queries in Excel applications frequently, but only to retrieve data from external datasource, when user opens the workbook, to consolidate or to split data, returned by external query/queries, or to consolidate some data from table in same workbook (p.e. mirror table with links to several workbooks, with gaps between data blocks). As rule I never use several queries on same sheet. I'm trying to keep the number of queries low, and allow the refreshing only on open and manually. In your case, better consider 2 options: a) use formulas (SUM(), COUNT(), SUMIF(), COUNTIF(), SUMPRODUCT() etc.) to calculate summary values (all calculations are made automatically, but when the number of formulas increases, the workbook is getting slow). b) create a procedure, which calculates the summary sheet, and is started p.e. from command button on sheet, and maybe on Open event too (the workbook is much faster, but the user has to remember to refresh the summary sheet). -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Gregory" wrote in message ... Yes, you are right. I have some arrays of input data (foundries and registries), which were received from other program sources over 16 orgs in our holding, then finance director preparing finance plan or other reports, dynamically changing input data and report forms (for example, hi describes [2,3,7,8][7-9][2^10-90|2^1000-1143,2^1148-3120,2^3135-5999] as profit of [2,3,7,8] orgs in 3-d quarter, and else) . Of cause, some times, he is making his work w/o internet, that means local realization of algorithm. If i don't resolve time-memory problem by excel ODBC, i should try another ODBC-complain local data-source, may be dbf or mysqld. Almost of all i prefer current realization, cause it's work normal (one time :)) "Arvi Laanemets" сообщил/сообщила в новостях следующее: ... Hi Am I right that you are running 200 separate queries with source table(s) in same workbook on same sheet? What do you want to achieve with this, I wonder! Am I right in my suspect, that you calculate some summary values from tables on other worksheets this way? Anyway, I'm sure there are less resource-consuming solutions available - maybe you describe, what kind of original data you have, and what do you want to get out of them. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Gregory" wrote in message ... Is this advice? ))) I so prefer to store my algorithm (very flexible finance planning sheets with my special formulas) which is very transportable Of cause, i can transfer data to server (with RPC or other technology), make recount, transfer back, but why i must do that? "Tim Williams" <saxifrax@pacbell*dot*net сообщил/сообщила в новостях следующее: ... Don't do it? Tim "Gregory" wrote in message ... I discover that when i recounting more then 200 QT on one sheet with this simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and "unstoppable" (closing frames, but not unload process) Can you give me any advice? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
Thanks to ALL!
Of cause, I also see wrong sides of that decision, may be some times i can use SPREADSHEET FUNCTIONS (like SUMPRODUCT), but when i want to allocate a group of costs in specified diapason over all orgs by criteria... Such is only sql or vba macro work. The main problem - WHY MEMORY DON'T CLEARED AFTER DESTROING QUERYTABLE. I think, that IF NO ESCAPE OF THAT PROBLEM EXISTS, then, may be, IT'S ONE OF ALL EXCEL DEVELOPERS MISTAKES. "Arvi Laanemets" сообщил/сообщила в новостях следующее: ... Hi I think an ODBC query is a wrong tool here. I myself use queries in Excel applications frequently, but only to retrieve data from external datasource, when user opens the workbook, to consolidate or to split data, returned by external query/queries, or to consolidate some data from table in same workbook (p.e. mirror table with links to several workbooks, with gaps between data blocks). As rule I never use several queries on same sheet. I'm trying to keep the number of queries low, and allow the refreshing only on open and manually. In your case, better consider 2 options: a) use formulas (SUM(), COUNT(), SUMIF(), COUNTIF(), SUMPRODUCT() etc.) to calculate summary values (all calculations are made automatically, but when the number of formulas increases, the workbook is getting slow). b) create a procedure, which calculates the summary sheet, and is started p.e. from command button on sheet, and maybe on Open event too (the workbook is much faster, but the user has to remember to refresh the summary sheet). -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Gregory" wrote in message ... Yes, you are right. I have some arrays of input data (foundries and registries), which were received from other program sources over 16 orgs in our holding, then finance director preparing finance plan or other reports, dynamically changing input data and report forms (for example, hi describes [2,3,7,8][7-9][2^10-90|2^1000-1143,2^1148-3120,2^3135-5999] as profit of [2,3,7,8] orgs in 3-d quarter, and else) . Of cause, some times, he is making his work w/o internet, that means local realization of algorithm. If i don't resolve time-memory problem by excel ODBC, i should try another ODBC-complain local data-source, may be dbf or mysqld. Almost of all i prefer current realization, cause it's work normal (one time :)) "Arvi Laanemets" сообщил/сообщила в новостях следующее: ... Hi Am I right that you are running 200 separate queries with source table(s) in same workbook on same sheet? What do you want to achieve with this, I wonder! Am I right in my suspect, that you calculate some summary values from tables on other worksheets this way? Anyway, I'm sure there are less resource-consuming solutions available - maybe you describe, what kind of original data you have, and what do you want to get out of them. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Gregory" wrote in message ... Is this advice? ))) I so prefer to store my algorithm (very flexible finance planning sheets with my special formulas) which is very transportable Of cause, i can transfer data to server (with RPC or other technology), make recount, transfer back, but why i must do that? "Tim Williams" <saxifrax@pacbell*dot*net сообщил/сообщила в новостях следующее: ... Don't do it? Tim "Gregory" wrote in message ... I discover that when i recounting more then 200 QT on one sheet with this simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and "unstoppable" (closing frames, but not unload process) Can you give me any advice? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
Hi
"Gregory" wrote in message ... Thanks to ALL! Of cause, I also see wrong sides of that decision, may be some times i can use SPREADSHEET FUNCTIONS (like SUMPRODUCT), but when i want to allocate a group of costs in specified diapason over all orgs by criteria... Such is A tip for using spreadsheet functions for such tasks. Let you have a list of org's on summary sheet. Select a org from list (activate cell). You can define a dynamic named range/formula, which calculates wanted summary value for this org - with cell reference to same cell in form $C#. Mostly it's possible to define such a formula so, that when you select another row, the value for this row is calculated, etc. Now into adjacent column you can enter something like =YourNamedFormula , and you get wanted value calculated for all org's. And have you tried to use Pivot table - from what you sayd, it looks like solution for you. only sql or vba macro work. The main problem - WHY MEMORY DON'T CLEARED AFTER DESTROING QUERYTABLE. I think, that IF NO ESCAPE OF THAT PROBLEM EXISTS, then, may be, IT'S ONE OF ALL EXCEL DEVELOPERS MISTAKES. I don't know, how MS Query handles memory after querytable is destroyed, but I don't advice to have any illusions there. Excel himself manages computer resources extremly badly - it simply grabs all what's available, can it use it or not, especially when running ODBC. And another example: I started with database design in DBase, and continued with FoxBase and then FoxPro. When I some years ago started to work with MS Office, I was surprised to find out, that queries like SELECT DISTINCT * FROM table1 WHERE field [NOT] IN (SELECT field FROM table2 WHERE condition=True) , which in FoxPro would be ready in a couple of seconds, needed minutes in ACCESS. It looks like SQL in MS Office is not optimized properly. Now I'm avoiding complex queries whenever it's possible, and p.e. instead query above I use the construct like SELECT DISTINCT * FROM table1 WHERE field [NOT] IN queryX where queryX=SELECT field FROM table2 WHERE condition=True -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
THANKS ALL.
Let's stop. I see, that I can't create 200 query tables on one sheet. That's all. I have created SO MACH user input forms based on Excel (cause it is "usable" for users), data from which transfers to web-server by XML-RPC, where stored and prepared for reports or other analytics. We also can transfer all patterns, created by my director and receive data back in one query table. It will work well and much faster then excel. THANKS ALL. "Arvi Laanemets" сообщил/сообщила в новостях следующее: ... Hi "Gregory" wrote in message ... Thanks to ALL! Of cause, I also see wrong sides of that decision, may be some times i can use SPREADSHEET FUNCTIONS (like SUMPRODUCT), but when i want to allocate a group of costs in specified diapason over all orgs by criteria... Such is A tip for using spreadsheet functions for such tasks. Let you have a list of org's on summary sheet. Select a org from list (activate cell). You can define a dynamic named range/formula, which calculates wanted summary value for this org - with cell reference to same cell in form $C#. Mostly it's possible to define such a formula so, that when you select another row, the value for this row is calculated, etc. Now into adjacent column you can enter something like =YourNamedFormula , and you get wanted value calculated for all org's. And have you tried to use Pivot table - from what you sayd, it looks like solution for you. only sql or vba macro work. The main problem - WHY MEMORY DON'T CLEARED AFTER DESTROING QUERYTABLE. I think, that IF NO ESCAPE OF THAT PROBLEM EXISTS, then, may be, IT'S ONE OF ALL EXCEL DEVELOPERS MISTAKES. I don't know, how MS Query handles memory after querytable is destroyed, but I don't advice to have any illusions there. Excel himself manages computer resources extremly badly - it simply grabs all what's available, can it use it or not, especially when running ODBC. And another example: I started with database design in DBase, and continued with FoxBase and then FoxPro. When I some years ago started to work with MS Office, I was surprised to find out, that queries like SELECT DISTINCT * FROM table1 WHERE field [NOT] IN (SELECT field FROM table2 WHERE condition=True) , which in FoxPro would be ready in a couple of seconds, needed minutes in ACCESS. It looks like SQL in MS Office is not optimized properly. Now I'm avoiding complex queries whenever it's possible, and p.e. instead query above I use the construct like SELECT DISTINCT * FROM table1 WHERE field [NOT] IN queryX where queryX=SELECT field FROM table2 WHERE condition=True -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel ODBC query tables memory overflow
Hi ALL!
My problem have SO STUPID decision - if i close datasource sheets (from which SELECT's are create) ALL IS OK- memory state doesn't change. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel query via ODBC, left joins on multiple tables | Excel Discussion (Misc queries) | |||
Excel query via ODBC cuts values | Excel Discussion (Misc queries) | |||
Calling Microsoft Excel ODBC Query | Excel Programming | |||
Excel ODBC Query | Excel Programming | |||
EXCEL AND ODBC AND QUERY | Excel Programming |