Huge Memory Consumption - Database Discussion Boards

perlmunger3-Jan-05 9:40

3-Jan-05 9:40

Your question is very confusing. Are you just wanting to populate a web form with data or do you want to dynamically create a web form and then populate it with data, or other?

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Where...

KORCARI1-Jan-05 1:22

KORCARI

1-Jan-05 1:22

Can you show me any links about working with ADO-DAO in Access 2002.I will be very grateful

SQL Server does not exist or access denied

nikneem200531-Dec-04 6:07

nikneem2005

31-Dec-04 6:07

Hey guys,

strange problem... when connecting to SQL Server (2000) from my WinXp (sp2) machine, i get a 'SQL Server does not exist or access denied' exception.

However, when i browse to the server in the server explorer (the SQL server runs on a diffrent machine) i CAN access the server. So I dragged and dropped the connection onto my (web) form and tried to open it at Page_Load() (ASP.NET) Same problem.

I know for sure my connection string is correct, I don't use integrated security, but log on with the 'sa' user and it's password. I know this user has enough rights, because I can browse the entire database needed using the server explorer as well as the enterprise manager. I think my winxp firewall blocks something, or the aspnet user (which runs the worker proces (aspnet_wp) for my website) hasn't got enough rights for network communication or so. I tried disabeling the winxp firewall without success, i'm pretty much out of solutions... anybody had this before, or knows what's going on?

Thnx in advance, Eduard

Re: SQL Server does not exist or access denied

kings_131-Dec-04 7:51

kings_1

31-Dec-04 7:51

Chek youer connection string see to what computer name is on to

Re: SQL Server does not exist or access denied

Colin Angus Mackay31-Dec-04 15:26

Colin Angus Mackay

31-Dec-04 15:26

Well, if you are using sa just for testing access to make sure you can see the SQL Server that is fine. You shouldn't use it normally as it represents a serious security risk.

If you can use the Enterprise Manager to browse to the SQL Server then the firewall isn't the problem.

If you are connecting use a SQL Server account, such as sa, then the access rights of the ASPNET account are not important - they only become important if you do use Windows Integrated Security, which you aren't.

In order to solve connection problems you need to understance how the ConnectionString works - relying on the IDE to generate one for you is useful only for quick throw away applications. I find that in production code it is a hinderance and causes more problems than it solves.

You should examine the ConnectionString[^]that has been generated. You need to look at 4 parts specifically. These are Server (or Data Source), Database (or Initial Catalog), Password and User ID. See the linked document for more information.

The Server must be set to the name of your SQL Server (this may be a named instance of SQL Server, in which case the server name is in the format MachineName/NamedInstance)

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: SQL Server does not exist or access denied

nikneem20051-Jan-05 4:00

nikneem2005

1-Jan-05 4:00

My current code

Try
objConn.ConnectionString = "Data Source=192.168.0.1;Initial Catalog=Website;User Id=sa; Password=password;"
objConn.Open()
Label1.Text = "OK"
Catch ex As Exception
Label1.Text = ex.Message
End Try

Should be OK. I've got only one instance of sql server running, so that shouldn't be the problem.

I tried creating a regular windows app. which runs under my local user, and still no result. At least this solved the ASPNET User Account issue. Even if I copy the connection string from the connection in my Server Explorer there's no result.. But i'm pretty sure my connectionstring is right...

I know using the 'sa' user isn't a good idea in production websites, but for testing issues, i know the user has enough access rights for sure, that's why using sa at this time.

I love it when a plan comes together

Re: SQL Server does not exist or access denied

nikneem20051-Jan-05 4:28

nikneem2005

1-Jan-05 4:28

What the f*ck! Created a new login, with only db owner rights on that very database and guess what, it works! I think there are some remote access problems with the sa account.

However, I was testing an win app, copying and pasting the connectionstring exactly onto a webform generates the same error.

I love it when a plan comes together

Huge Memory Consumption

Developer567831-Dec-04 0:41

Developer5678

31-Dec-04 0:41

Hello,

I'm developing an application in VC++ using MFC. I use MS Access as the back end. I use the following function for retrieving some data from the database. This function is called in a loop around 20 times (one query per table).

I'm observing a strange problem. The first time when the set of 20 queries are executed the total memory allocated within this function comes to around 50 MB (as shown by the Task Manager). Now, without closing the application, if I repeat the same set of queries (I can do it through a menu item) the memory allocation within the function is around 2 MB only. Now onwards, If I repeat the same set of queries any no: of times, each time the memory
allocation is only around 2 MB.

If I close the application and start it again, the same behaviour happens. That is, only the first time a huge amount of memory is consumed.

Please let me know if there is any problem with this code that causes this
strange behavior.

Thanks & Regards,
Nishanth.

int CMyDataBase::QueryRecordsFromDatabase(
CArray <CStringArray*, CStringArray*>& p_arRecordPtrs,
const CString& p_csQuery)
{
int nRetVal(0);

_RecordsetPtr pRecordSet;

try
{
pRecordSet.CreateInstance(__uuidof(Recordset));
pRecordSet->Open(_bstr_t(p_csQuery),
m_pDatabase.GetInterfacePtr(),
adOpenDynamic,adLockOptimistic, adCmdText);

_variant_t Index;
_variant_t Indexval;
Index.vt = VT_I2;

int nTotalFields = pRecordSet->GetFields()->Count;

while(!pRecordSet->adoEOF)
{
CStringArray* pRecord = new CStringArray;

pRecord->SetSize(nTotalFields);

for (int nEachField = 0 ;
nEachField < nTotalFields; ++nEachField)
{
Index.iVal = nEachField;

Indexval =
pRecordSet->Fields->GetItem(Index)->Value;

CString csValue = (char*)_bstr_t(Indexval);
pRecord->SetAt(nEachField, csValue);
}

p_arRecordPtrs.Add(pRecord);

try
{
pRecordSet->MoveNext();
}

catch(_com_error &e)
{
m_csError = (char*) e.Description();
}
}

pRecordSet->Close();
pRecordSet.Release();

nRetVal = 1;
}

catch (_com_error &e)
{
m_csError = (char*) e.Description();
}
catch (...)
{
m_csError = "Unknown exception";
}

return nRetVal;
}

Re: Huge Memory Consumption

Mike Dimmick2-Jan-05 6:16

Mike Dimmick

2-Jan-05 6:16

You need to understand what the Task Manager columns measure, and how the system allocates memory. The 'Mem Usage' column indicates the process's working set size - the amount of the process's address space which is currently backed by physical memory. This value can change up or down depending on the demand from this or other processes, and isn't really a reflection of how much memory your process has actually allocated. This value includes pages that contain code and data from your process's executable and DLLs. It also includes pages that are shared with other processes, for example memory-mapped files. The sum of the Mem Usage column will normally exceed (Total Memory - Available - System Cache) because pages from memory-mapped files and DLLs are counted against every process that references them.

The system adjusts your process's working set based on memory demand and heuristics, trying to keep a pool of free memory to allow fast allocations without needing to scan all processes at the point memory is required, and to keep a reasonably-sized disk cache. If there's enough free memory, an increase of 50MB on this value is not important.

The 'VM Size' column shows the number of bytes allocated to your process (the same information is exposed through the Process: Private Bytes performance counter). However, this measures virtual memory allocations - pages of virtual address space allocated using VirtualAlloc. Because of how the heap works, this may not go down when you return memory to the heap (by calling free, using delete, etc).

The heap is a mechanism for allocating arbitrarily-sized blocks of memory. The heap has to get that memory from somewhere, and it does so by calling VirtualAlloc. Typically the initial heap allocation is 1MB; once this 1MB is exhausted, more virtual memory - typically not contiguous with earlier allocations - is allocated. In newer versions of Windows, the heap maintains free lists for blocks of memory of a given size; doing this allows the heap manager to allocate blocks smaller than that size quickly by taking the first block from the free list, rather than searching the heap for a block of the right size. When the block is freed, it is returned to the top of the free list, i.e. it will be the next block of this size to be allocated (this helps cache locality, typically).

The heap can become fragmented. Just like your hard disk, you can get into a situation where there's no room for a large allocation in the existing heap, since all the free blocks are smaller. The heap then has to grow to accommodate the large allocation, even if the total of all the free blocks is larger, because the heap cannot move allocated blocks around to find a larger free space. The best way to handle this is simply not to allocate so much memory.

I think you're looking at the Mem Usage column, and what you're seeing on the first query is simply ADO, the OLE DB provider for your database (MS Jet), your database being loaded into memory, and the heap growing to accommodate the allocations for the data. If you're using the MSDASQL provider, you've got more layers: the MSDASQL OLE DB provider for ODBC, the ODBC driver stack, and the ODBC MS Jet driver. If you are using MSDASQL you should consider using the Jet provider (Microsoft.Jet.OLEDB.4.0) instead.

I can see a few places where you're allocating more memory than you need. CString has constructors which take a wide-character (Unicode) string. Your intermediate casts to char*, when allocating csValue and m_csError, cause an extra allocation and, if you're compiling a Unicode build, an extra conversion each way. These should be unnecessary. Remember that BSTRs are always wide-character strings. If a cast is necessary, cast to LPCWSTR rather than char*.

You could also consider converting the return value of GetItem()->Value directly, rather than copying to an intermediate _variant_t. This copy operation causes the underlying string to be copied, if the variant already holds a string, which is unnecessary because you don't use the variant again after that.

Stability. What an interesting concept. -- Chris Maunder

Re: Huge Memory Consumption

Developer56783-Jan-05 2:45

Developer5678

3-Jan-05 2:45

Thanks for the detailed reply.

The problem here is the initial consumption is lineraly proportional to the no: of records in the tables. If I load ten times more data than I have it now, then the intial consumption goes more than 500 MB. It goes out of memory when I try to execute the queries on the database which has more data.

"I think you're looking at the Mem Usage column, and what you're seeing on the first query is simply ADO, the OLE DB provider for your database (MS Jet), your database being loaded into memory, and the heap growing to accommodate the allocations for the data. "

I didn't get what exactly you meant by the above sentence. After executing the set of 20 queries once, if I close the current database through the application and open another database of around the same size (without closing the application), still, the subsequent queries seem to consume less amount of memory.

Please let me know if you have some suggestions.

How

KORCARI31-Dec-04 0:09

KORCARI

31-Dec-04 0:09

I have to change an Access 2000 database in a SQL Server
2000 project and its DAO code in ADO.Can you show me some links where I can find instructions about this topic cause Im experiencing a big trouble on it.Thank you

Re: How

Rob Graham31-Dec-04 3:45

Rob Graham

31-Dec-04 3:45

This[^]link has some good info in that area, but unfortuately has little to say about actual code conversion.

If the old application is DAO code, you may have significat re-write to do, since I think DAO was fairly Access specific. ADO is more generic, and I would expect that you should have little to do besides move the data and change the connection strings (unless of course, it is lousy ADO code with lots of internal database specific assumptions). I have moved ADO apps from Access to SQL and little other than connection string changes were needed.

Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright

Where....

KORCARI31-Dec-04 0:06

KORCARI

31-Dec-04 0:06

I have to change an Access 2000 database in a SQL Server 2000 project and its DAO code in ADO.
Can you tell me some links where I can find information about this topics cause Im a beginner in this field and Im experiencing a big trouble on it.Thank you.

MySQL GRANT SELECT

alex.barylski30-Dec-04 15:09

alex.barylski

30-Dec-04 15:09

IS it possible to limit the number results returned on a TABLE?

I have a passwords table which when queried not matter the SQL statement, should only EVER return a single row. Regardless if a injection attack somehow is executed with a SELECT * FROM passwords.

So now you understand why SELECT * FROM pwd_table LIMIT 1 won't work.

I noticed there are some things I can do such as limit the number of queries/hour, but this isn't quite what I had in mind.

It's frustrating being a genius and living the life of a moron!!!

Re: MySQL GRANT SELECT

WoutL31-Dec-04 0:46

WoutL

31-Dec-04 0:46

Like:
Select top 1 * from pwd_table

Wout Louwers

Re: MySQL GRANT SELECT

Colin Angus Mackay31-Dec-04 15:46

Colin Angus Mackay

31-Dec-04 15:46

As Wout Louwers has said, using SELECT TOP 1 will guarantee that only one row is returned. However, that is only for one query, and will not help if, somehow, an injection attack is occurring.

What you need to do is to make sure that that the account being used to access your SQL Server does not have any rights to query or modify the table(s). If the account that is accessing SQL Server has no rights to query or modify the table they cannot attack it. (By "query" I mean a SELECT statement, and by "modify" I mean INSERT, UPDATE or DELETE).

In order to actually check a users validity, or create a new user and so on, you will need to create stored procedures for the operations you wish to perform on the table. You then give the permission to EXECUTE the stored procedures to the accounts that you need.

The way SQL Server security works is that if the user attempts to execute a stored procedure then the SQL Server with check that user is authorised to do that. If so then they stored procedure can run and, normally, no further checks are made. This means that a user can be permitted to use a stored procedure that uses tables that the user does not have access to. This protects the tables as the stored procedure can make additional checks to ensure that the data is valid before the request is performed. It also means that only the operations you specify in the stored procedures can be carried out.

I said that "normally" the security is not checked again when the system discovers that the user has permission to execute the stored procedure. There are cases where additional checks are made. One example is if some dynamic SQL is executed inside a stored procedure. Dynamic SQL is where the stored procedure takes information from the parametes or from tables in the database and constructs a new query internally in a string, then executes the SQL in the string. Dynamic SQL is a source for secondary injection attacks. These are often more problematic to trace to the source because the source of the attack can lay dormant in the database for some time before becoming activated.

I hope I've managed to answer your question, and anticipate follow up questions. Let me know if you need more.

[EDIT]
Sorry, I just realised you were talking about MySQL and not SQL Server. The principles are more or less the same for all databases and should work if MySQL supports stored procedures - It was in the pipeline the last time I looked, but I don't know if it is available yet.
[/EDIT]

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

TOPLESS LADIES INSIDE - SQL

HarryBo30-Dec-04 9:10

HarryBo

30-Dec-04 9:10

HA HA I LIED

Good day to you all and i hope you are well.

Not a direct code question guys, just in need of advice.

Imagine this scenario if you will,

You have a internet server with a SQL Server 2000 database and a client with a MSDE installation. Both are running the same database say MYDATABASE.

MYDATABASE on server is very large, say 100mb.

The client needs one table from the server to do the work they wish to do on their local machine.
They want to just get that one table (and all related obviously) updated on their machine as they don't need the full database as that would take ages (being 100mb).

How would you suggest passing that one table to the client, the client app updating the database on the local machine. Then, once they have done their work they re-connect to the internet server pass back the data they have added/updated and the server then updates accordingly.

Any help would be greatly appreciated.

My thoughts and ideas so far - DTS (not used this before though), dynamically formed SQL (clunky, unreliable).

Pleaaaaaseeee Help

Many Thanks

Harry

Re: TOPLESS LADIES INSIDE - SQL

Javier Lozano1-Jan-05 13:44

Javier Lozano

1-Jan-05 13:44

Please refrain from using that kind of title on this forum. If you expect for people to take your post seriously, please post it with a meaningful name.

~Javier Lozano
(blog)

Oracle forms 6i problem

missnazar30-Dec-04 4:00

missnazar

30-Dec-04 4:00

I have installed Oracle 8i Release 3 (8.1.7)...I want to install now D2K ( Oracle forms 6i ) ...On inserting the installation CD...the "Oracle installer" screen appears ..then "Oracle installation settings" form appears ..Here we have to specify

Company Name:xyz

Oracle home details

Name: OraHome81

Location: C:\oracle\ora81

Language: English

With theses entries when I click OK button, the following error message with the Title "General Information" appears:

The location specified "C:\oracle\ora81", is already used as an Oracle Home for 'Oracle 817 Production'.
It can not be used as an Oracle home for 'nt'.

The OS am using is Windows 2000 Professional with
Pentium 4 ...Please help me out...Thanking you in advance.

Stored Procedure PRoblem

percyvimal30-Dec-04 2:15

percyvimal

30-Dec-04 2:15

Hello
I planned to genralize a stored procedure where i can pass the table name field names as parameter to the storedprocedure along with the values and then use those information to add or update the table which is passed as parameter. My stored procedure look like this

CREATE PROCEDURE Add_Edit_GSupportTables(@AddMode bit,@GenCode int,@GenName varchar(100),@GenTable varchar(50),@GenCodeField varchar(50),@GenNameField varchar(50)) as
declare @AutoCode numeric;
if @AddMode = 1
begin
select @AutoCode = max(@GenCodeField) from @GenTable
set @AutoCode = isnull(@AutoCode ,0)
set @AutoCode = @AutoCode +1
Insert Into @GenTable values(@AutoCode,@GenName)

end
else
begin
update @GenTable set @GenNameField =@GenName where @GenCodeField = @GenCode
end

GO

but it gives incorrect syntax near @GenTable

How to do so that this stored procedure can be used as functoin to add / edit

with regards
vimal

Help in need is the help indeed

Re: Stored Procedure PRoblem

Mike Dimmick30-Dec-04 3:50

Mike Dimmick

30-Dec-04 3:50

I'd recommend splitting this into two procedures, add and edit. The query processor will either have to build one query plan with redundant code, or, more likely, recompile the query plan every time you swap the value of @AddMode.

As for your specific problem, you can only parameterize values. You can't parameterize schema. If you have to, you've probably designed your database incorrectly. Tables are not objects. Creating and dropping tables are very expensive operations.

If you absolutely must do this, you should use string concatenation to form the schema part of the query, then use sp_executesql to execute it supplying the parameters, e.g.:

DECLARE @query varchar(2000)
SET @query = 'UPDATE ' + @GenTable + 
             ' SET ' + @GenNameField + ' = @GenName WHERE ' + 
             @GenCodeField + ' = @GenCode'

EXEC sp_executesql @query, 
      N'@GenName varchar(100), @GenCode int',
      @GenName, @GenCode

Stability. What an interesting concept. -- Chris Maunder

Re: Stored Procedure PRoblem

abbaskaya30-Dec-04 4:29

abbaskaya

30-Dec-04 4:29

Example:

Create procedure prg.ExamleProcedure
@tabloname varchar(50),
@data int
as
declare @stmt varchar(8000)
select @stmt = 'insert into ' + @tabloname + ' (column1, column2) ' +
'select ' + @data + ' , NULL'

Exec (@stmt)

You can solve your problem like this.

abbaskaya

Re: Stored Procedure PRoblem

abbaskaya30-Dec-04 4:30

abbaskaya

30-Dec-04 4:30

like this.

abbaskaya

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.