CRecordset takes 10 seconds to open

Hi All

I'm new to sql server. I have built simple database apps using MFC CRecordset over MS Access. I'm tying to learn about SQL server by building a simple app using MFC CRecordset in Visual Studio 2005.

I have multiple CRecordset classes within my app. When I developed the app over MS Access I read somewhere that it was better to have a single database object that is shared by multiple recordsets. So I ended up with something like this -

Code Snippet

// In the class header - 2 CRecordset derived classes

CMyRecordSet m_MyRecordSet; // Connects to the database using

// its "GetDefaultConnect" string

CAnotherSet * m_pAnotherSet;

// Within a "CreateRecordSets" method

if (!m_MyRecordSet.IsOpen()) m_MyRecordSet.Open();

// This works fine

m_pAnotherSet = new CAnotherSet (m_MyRecordSet.m_pDatabase);

m_pAnotherSet->Open();

// This open takes 10s to execute,

// there are 10 rows of data in the table

I have about 6 recordsets that I create in this way, there is a tiny amount of data in the database. The open for the next record sets return immediately.

The same app over MS Access works fine - no delays. SQLServer Express and Access are both running on my development PC.

Can anyone tell me why this call takes so long and whether there is a way of avoiding it?

Subsequent calls to the database return immediately.

Thanks

Alec

SQL Server 2005 Express 9.00.3042.00

Microsoft SQL Server Management Studio Express 9.00.2047.00

Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

I thought that I had found the cause of this but I've only found out how to reproduce it.

If I change the character set to unicode in the Visual Studio 2005 project settings the problem goes away. (This is an MFC SDI form view application.) I had originally creating my sql database tables by exporting them from Access and this had created nvarchars. I changed these to varchars but still I get the 10s delay when the project is built with "No Character set" selected. I'm not sure what the side affects of leaving unicode selected may be so I don't really want to change this without understanding the underlying cause.

In the CRecorset code generated by visual studio it says

// The string types below (if present) reflect the actual data type of the

// database field - CStringA for ANSI datatypes and CStringW for Unicode

// datatypes. This is to prevent the ODBC driver from performing potentially

// unnecessary conversions. If you wish, you may change these members to

// CString types and the ODBC driver will perform all necessary conversions.

// (Note: You must use an ODBC driver version that is version 3.5 or greater

// to support both Unicode and these conversions).

My ODBC driver version is 2000.85.1117.00 - am I looking at bthe wrong version number? (this is from the ODBC create new datasource window).

I have varchars being loaded in CString so is there any conversion necessary? (even if there was this convserion should not take so long).

Can anyone explain the delay related to selected charatcter set?

Thanks

Alec

Creating Time Dimension for seconds

I'm creating analysis cubes on data that needs to reflect it's distribution over time during a 4 hour period.

I'd like to create a Time Dimension for "Every 15 seconds"

I've already got a DIM_Time table that includes actual event times (down to milliseconds)

Do I just need to create fields on that data for the 0:15 block that the particular event falls in, and create records for every 0:15 even if there isn't a related row in the measures table?

So that...

Measures
ID TimeId Measure
1 1 100
2 2 200

DIM_Time
TimeId Time
1 0:01.1234
2 0:31.2345
3 0:15
4 0:30
5 0:45
6 1:00
7 1:15
etc...

Hi Greg,

You're on the right track. The reason you would put the 15 second block even if there isn't an actual time for that is later on down the road there may be a fact for that and you would simply add the fact without have to also add the dimension member. You also may want to see for some reason the times that don't have data.

Depending on the requirements, I might break the Min and Seconds out in seperate fields. Like

TimeID Hour Min Sec Actual Time

1 0 0 0 0:10:00

2 0 0 15

3 0 0 45

4 0 1 0

Depending if the facts needs to roll up to a Min. and Hour.

Hope this helps,

David Botzenhart

|||Thanks! I appreciate your clarifications on the table structure