Read/Write Performance

Hello,

We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express.

We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine.

We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same.

Is there something we need to look into as far as read write speed/performance goes ?

TIA,

Avinash

Hi Avinash,

Could you provide a bit more information about how you determined the time it took to run the queries? Understanding your testing methodology will help determine if it is contributing or not.

Additionally, you mention you're using the auto attach feature, are you using User Instances as well? When using the VS UI to create connection to a database, the connection string specifies User Instance = True. This should be fine, but it's important to know.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hello Mike Wachal,

Yes we use User Instance and Auto Attach in the connection string to express.

At this time, I dont have a very 'scientific' or for that matter a very solid way to test out the performance. My question came from a general observation and thought I'd bounce it off the expert community to see if there was some caveats built into the use of express particularly with the auto attach mode.

Like I've already mentioned - our general observation is that queries 'Seem' to take longer on express both read and write when compared to 2005 server. Again, this may be a configuration thing aswell. But we are running the default configuration of express as done from within VS.Net 2005 setup and have made no changes what so ever.

On some counts we've put the start time and end time of execution in trace messages and have found the difference in execution times.

Thats all I have at this time,

Thanks and Regards,

Avinash

|||

Thanks Avinash,

In general, SQL Express should perform similarly to other Editions of SQL Server, but we do have limitation that might affect performance. For one thing, SQL Express will only use a single CPU and will only address 1 GB of RAM. If your hardware has more than this, then you could see a performance difference because the non-Express edition would be able to use the extra hardware components to increass performance over Express Edition.

If you're hardware is the same and within the limitations of SQL Express, I'm not sure what could be the issue without knowing more about the specific queries and data that is being queried.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hello Mike,

Many thanks for your response. In our case both the server and the client run the exact same hardware. They are (both) running on 1GB RAM.

I'll watch out for any further issues I may run into - and then bring them to your notice with all the data that I can provide.

Thanks,

Avinash

|||

OK Avinash,

Good luck with this.

As you find specific queries that perform differently you might want to bring those specific queries up in the SQL Database Engine forum. The folks in that forum will likely have some additional ideas both on tuning your queries and why they may perform differently on different Editions.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

sql

read/write BLOBS in sqlser using asp.net

here i have some problem related to BLOBS in sqlserver
i need to store .bmp or .jpg files in sqlserver2000 and using
asp.net i need to retrive that images and show in a webform.
if user want to insert a new Image into database i need to do
it from frontend using asp.net
just like i need add,update,edit images using asp.net
can any one give me idea how to approch.

thanks in advance..
-SridharHere's a KnowledgeBase article that explains it:HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual Basic .NET. It includes links to equivalent versions of the article for C++ and C#.

Don

Read xml file, store data in db. What could be easier?

OK guys,
I am going to receive an xml doc and am going to have to parse the data
values and store them in the database. About 10 db tables are targets for the
data, with repeating groups of data and such. I have not done this with Sql
Server before. I need to be pointed in the right direction. So start
pointing!
Thanks,
Michael
These xpath query samples are suitable for COM and
for .NET.
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:58E7DD5B-CEAF-4677-902A-0A7BBBF0DA30@.microsoft.com...
> OK guys,
> I am going to receive an xml doc and am going to have to parse the data
> values and store them in the database. About 10 db tables are targets for
> the
> data, with repeating groups of data and such. I have not done this with
> Sql
> Server before. I need to be pointed in the right direction. So start
> pointing!
> Thanks,
> Michael
sql

Re: How do you encrypt a password column?

Hi all,
How do you encrypt a password column in SQL Server 2000 table?
I would like to store something like *** in the column.
Thanks much!do you submit the same question every couple of weeks to drive me insane?|||I'd check here (http://www.dbforums.com/showthread.php?t=1099654), or here (http://www.dbforums.com/showthread.php?t=1095162) for a couple of answers.

-PatP|||I beleive it's an agent that wakes up once a week...|||Try this:

create function EncryptPassword(@.PasswordString varchar(20))
returns char(10)
as
begin
declare @.NewPassword varchar(20)
set @.NewPassword = isnull(nullif(@.PasswordString, @.PasswordString), '**********')
return @.NewPassword
end|||Here is an even stronger algorithm. It uses a single-pass one-way encryption hash that is very secure. It is impossible to decrypt. As far as I know, this method has never been cracked:

drop function EncryptPassword
go
create function EncryptPassword(@.PasswordString varchar(20))
returns char(10)
as
begin
declare @.ASCIICounter as integer
set @.PasswordString = upper(@.PasswordString)
set @.ASCIICounter = ascii('A')

while @.ASCIICounter <= ascii('Z')
begin
set @.PasswordString = replace(@.PasswordString, char(@.ASCIICounter), '*')
set @.ASCIICounter = @.ASCIICounter + 1
end

return @.PasswordString
end|||Hi,

To those who replied with help I appreciated always.
To those who replied with criticisms don't reply at all. Who are you to judge other people. Either help or don't.
Thanks!|||Now look here. I posted some help when you asked this question 2 times ago. . In fact several fine solutions were suggested to your problem each time you posted this.

I like helping people, but it gets a little fustrating answering the same question from the same person all of the time.

If you do not understand the answers, say so. We will explain further but don't keep posting the same question. It's annoying.|||You may have seen the post more than once someone may not.
The possible solutions may not be the one One is seeking for.
Its probably annoying or frustrating what you think is the problem. No one should be publicly humiliated.|||YOU (alicejwz) need to quit posting the same d@.mn question over and over again.

Very knowledgeable, very intelligent, very patient, and very generous people have already given you the ONLY ANSWER YOU WILL GET.

SEVERAL TIMES!

And frankly, it is INSULTING that you just keep posting the same question hoping somebody new will give you a better answer.

You think the people on this forum aren't smart enough to know the answer to such a simple question? Then go find some other forum to pester.

Bleh!|||Hi,

To those who replied with help I appreciated always.
To those who replied with criticisms don't reply at all. Who are you to judge other people. Either help or don't.
Thanks!

Ummm...do you know the difference?

And lighten up Francis|||YOU (alicejwz) need to quit posting the same d@.mn question over and over again.

Very knowledgeable, very intelligent, very patient, and very generous people have already given you the ONLY ANSWER YOU WILL GET.

SEVERAL TIMES!

And frankly, it is INSULTING that you just keep posting the same question hoping somebody new will give you a better answer.

You think the people on this forum aren't smart enough to know the answer to such a simple question? Then go find some other forum to pester.

Bleh!

Yo dude...who replied with help?

EDIT: And where's Karolyn Been?

I think that's her login|||You may have seen the post more than once someone may not.
The possible solutions may not be the one One is seeking for.
Its probably annoying or frustrating what you think is the problem. No one should be publicly humiliated.If you just want to bring the thread back to the top of the list, then "bump" it. Add a new posting at the end and simply comment that you are still looking for another answer, or even just the word "bump" by itself. That way the folks who've already responded will easily recognize both that they've already answered the question, and that you weren't satisfied with the answers you've gotten so far.

The people who reply to questions are a scarce resource... Well over 90% of the people that visit will never post, and another 9% will only post questions. The remainder is all that we've got to provide answers, so anything that irritates them is a danger to the forum itself. I'm not trying to tell you to bow and scrape to the posters, but please consider that they volunteer to help with problems... If you want that resource available quickly, easily, and for free, you need to husband it carefully!

Nobody is trying to humiliate you (yet), you'll know much more clearly when that happens! The repeated posting of the same question is like repeatedly asking the same question at a party... It is often ignored the first time it is repeated, sets people to whispering after that, and gets a rather stern lecture if it goes on too long. Right now you're well into the whispering stage with this question.

-PatP|||The possible solutions may not be the one One is seeking for.

I posted something like that on the Pagan forum! (It seemed to be the right place for it.)

:)|||I posted something like that on the Pagan forum! (It seemed to be the right place for it.)

:)

Do you mean the Oracle forum?|||If you just want to bring the thread back to the top of the list, then "bump" it. Add a new posting at the end and simply comment that you are still looking for another answer, or even just the word "bump" by itself. That way the folks who've already responded will easily recognize both that they've already answered the question, and that you weren't satisfied with the answers you've gotten so far.be very careful when doing this

if you capriciously just "bump" for no stated reason, your bumping post might be deleted by a moderator

in other words, your thread will be left alone, but the last "bump" will be removed|||be very careful when doing this

if you capriciously just "bump" for no stated reason, your bumping post might be deleted by a moderator

in other words, your thread will be left alone, but the last "bump" will be removed

Why is this?

You got a rule book?|||'cause Sheriff Rudy says so, that's why.

Though up there I guess they call them "Mounties".

Raw File Destination Access Mode Filename from Variable Problem

I have a raw file destination and am using a variable to store the filename. In an earlier task, I create the value in the variable. User:Filename ... set to C:\Test.txt.

When I run the package, I get the "Error: 0xC0202070 at DFT Tekelec Call Events, RFD Tekelec [1365]: The file name property is not valid. The file name is a device or contains invalid characters". error. I then set a breakpoint to examine my variables on the DataFlow pre-execute event and found my variable showing the value "C:\\Text.txt" ... so apparently XMLA is adding the \ escape character when it stores the value in the variable but not retracting it when it uses the value as the filename.

What am I missing? Can I not use pathing in the variable? And if that's the case, how do I specify a path. Went back through my Rational Guide to Scripting SSIS but did not find this addressed specifically ... my second option being build the fiilename by script and set the raw file destination property directly via script.

The Watch window shows the C style encoded value, so \ becomes \\ in this display. Using a variable for the Raw File does work, and if setting a literal value it should just work. Have you perhaps got an expression on that variable? Could that confuse things?

Where does XMLA come into this?

Rapid Prototype Software?

Can someone recommend a software prototyping tool suitable for a web front
end that would collect and store data in a SQL database. I'm looking for
something under $500 that would simulate drop down menus, hyperlinks,
checkboxes and the like. Any suggestions would be appreciated. Thanks, Pancho
try this:
http://www.microsoft.com/france/msdn...s/default.mspx
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0737A734-3663-44B1-AA37-2EEB8B969442@.microsoft.com...
> Can someone recommend a software prototyping tool suitable for a web front
> end that would collect and store data in a SQL database. I'm looking for
> something under $500 that would simulate drop down menus, hyperlinks,
> checkboxes and the like. Any suggestions would be appreciated. Thanks,
> Pancho
|||Parait interessant. Merci, Pancho
"Jéjé" wrote:

> try this:
> http://www.microsoft.com/france/msdn...s/default.mspx
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:0737A734-3663-44B1-AA37-2EEB8B969442@.microsoft.com...
>
>

Rapid Prototype Software?

Can someone recommend a software prototyping tool suitable for a web front
end that would collect and store data in a SQL database. I'm looking for
something under $500 that would simulate drop down menus, hyperlinks,
checkboxes and the like. Any suggestions would be appreciated. Thanks, Panc
hotry this:
http://www.microsoft.com/france/msd...rs/default.mspx
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0737A734-3663-44B1-AA37-2EEB8B969442@.microsoft.com...
> Can someone recommend a software prototyping tool suitable for a web front
> end that would collect and store data in a SQL database. I'm looking for
> something under $500 that would simulate drop down menus, hyperlinks,
> checkboxes and the like. Any suggestions would be appreciated. Thanks,
> Pancho|||Parait interessant. Merci, Pancho
"Jéjé" wrote:

> try this:
> http://www.microsoft.com/france/msd...rs/default.mspx
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:0737A734-3663-44B1-AA37-2EEB8B969442@.microsoft.com...
>
>

Rapid Prototype Software?

Can someone recommend a software prototyping tool suitable for a web front
end that would collect and store data in a SQL database. I'm looking for
something under $500 that would simulate drop down menus, hyperlinks,
checkboxes and the like. Any suggestions would be appreciated. Thanks, Panchotry this:
http://www.microsoft.com/france/msdn/olymars/default.mspx
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0737A734-3663-44B1-AA37-2EEB8B969442@.microsoft.com...
> Can someone recommend a software prototyping tool suitable for a web front
> end that would collect and store data in a SQL database. I'm looking for
> something under $500 that would simulate drop down menus, hyperlinks,
> checkboxes and the like. Any suggestions would be appreciated. Thanks,
> Pancho|||Parait interessant. Merci, Pancho
"Jéjé" wrote:
> try this:
> http://www.microsoft.com/france/msdn/olymars/default.mspx
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:0737A734-3663-44B1-AA37-2EEB8B969442@.microsoft.com...
> > Can someone recommend a software prototyping tool suitable for a web front
> > end that would collect and store data in a SQL database. I'm looking for
> > something under $500 that would simulate drop down menus, hyperlinks,
> > checkboxes and the like. Any suggestions would be appreciated. Thanks,
> > Pancho
>
>