best practice for lookup

say i have a customer.aspx that allows a user to enter in customer data.

on customer.aspx, i have dropdownSalesRep which allows the user to associate a sales rep with the customer

but some customers come to directly, and not thru a sales rep, so I want the user to be able to specify "none"

Is it best to have a dummy record in my SalesReps table called "none" with an ID of say "999", or is there some other better way to deal with this?

I think this is more of a business decision. When you have to do reporting later on to track the sales, would you want to see "none" under sales rep?

|||

well, probably not, so assuming you don't - then is there even another way dealing with it assuming that in the underlying db, a customer must have an associated sales rep? in that reporting scenario, you'd have to write sql to filter out the "dummies" i guess.

|||

NuJoizey:

...assuming that in the underlying db, a customer must have an associated sales rep?

If you have to code according to that assumption, then you really have no choice than to use some sort of default value in your application with an Id of "999" or whichever you are comfortable with. Jjust make sure it doesnt get repeated. You can also use a negative value like -1 so there is no conflict with any number generated by SQL Server if your table grows.

|||

Another way to handle this is to store NULL in the sales rep ID. This is, in a sense, the more elegant solution since NULL means not known and you can do this and still maintain the foreign key relationship with your sales rep master table

However, many people don't like using NULL values because of their unintuitive behavior in select statements (eg, "select * from customer where SalesRepId <> 3" will NOT return rows where SalesRepId is NULL because NULL= unknown and if something is unknown it justmight be 3).

My advice is to follow ndinakar's suggestion and use a special value like -1

|||

great - thank you for the discussions

Best Practice for Counts/Distinct Count Measures for Filtered Data

Let's say the Count/Distinct Count measure is based on filtered data.

When the situation allows, is it better to do a CASE statement with 1s and 0s from the table in the DSV and then do a SUM on this field? Or is it better to create a new Named Query in the DSV filtering out the data through the WHERE clause based off of the original table and then do a DISTINCT COUNT measure on the newly created Named Query?

If there's no clear cut answer as to what is the "better" approach, please offer up the advantages and disadvantages for both. My preference is best performance.

An Example:

Measure: Active SKU Count
Products dimension has product_key, SKU and Status

In the DSV for the Products dimension, should I add a CASE statement:

CASE WHEN [Status] = 'Active' THEN 1 ELSE 0 END AS [Active Status]

Then create a new SUM measure based on the Active Status column.

OR

Create a new Named Query in the DSV:

SELECT * from Products WHERE Status = 'Active'

Then create a new DISTINCT COUNT measure based on the SKU column from the new Named Query?

These 2 alternatives do not produce the same results. If a given SKU has fact records in Jan, Feb, and Mar, the first approach using SUM would return a value of 3 for Calendar Quarter 1. Then second approach based on a distinct count measure would return 1 (which is probably what you want)

Distinct count measures should be implemented in their own measure group anyway, so if you only ever need the active count then filtering out the non-active records is a good option.

If you need to get distinct counts of both active and inactive SKUs then you would be better off adding the Active flag as an attribute to the product dimension and implementing it as a slowly changing dimension.

|||Darren,

I had a feeling that these 2 alternatives would not produce the same results when I was typing up the question. Perhaps my example scenario wasn't a good one, because the limited testing with my applicable situation yielded the same results.

In any event, I will just go ahead and go forward with the separate measure group with the new Named Query approach.

Thanks!

Best approach

I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along.

Source server SQL 2000 database allows NULL values in columns.

Destination Server also SQL 2000 but the database required a value in each column.

So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever.

I believe I should use a Derived Column and an expression ISNULL to accomplish what I want.

Maybe there is a better way. Suggestion and comment appreciated.

Ryan

Try using the T-SQL ISNULL() function in your initial query, replacing any null values with an empty string or whatever...

ISNULL(myField,'')

Benefits of using SQL over XML

Hi,

I have a question relating to XML and SQL. My company currently runs a website which allows its clients to log in, view their accounts and transaction history online. The website is totally read only with the exception of changing passwords.

The data is taken from our back office system overnight which runs an oracle 8i database (we cannot like our website to the database due to the agreement we have in place with our software supplier). The data is written to a CSV file which is then converted into XML. The XML file is saved to the webserver and is referenced by the website.

The structure of the website has a relationship where the Client has a Manager who can see their clients accounts, a Branch level that can see all of their Managers and the underlying clients and then finally a company level that sees everything.

We are finding that using XML is causing a real issue in performance and I was wondering if migrating the website to SQL server would improve the performance of the queries etc .

Any advice would be gratefully recieved

Lee

It really depends on two things: The application and the version of SQL Server you are using. For certain input/retrieval methods, XML can actually be faster than using direct database calls. SQL Server 2005 has native XML features, which you can read more about here:

http://www.sqlsummit.com/People/MRys.htm

Buck

|||

The thing is that our website is taking considerably longer to return results using XML. Our software provider can provide a website which uses Oracle and an example website using test data seems to query and return the data back in far less time then ours using XML. But this site is a lot more costly option and does not provide all the functionalty we require. The main reason for the performance increase is that we want to be able to use the website internally for our branches and front office staff, so performance is key it will have about 20 - 30 users. We are planning to do this because we are unable to restrict access to parts of our back office system from the front office staff. The problem with the performance of the website currently means that the staff will have to deal with a sluggish system.

Our website designer has said that he would have to rewrite the website to change it from XML to SQL, would XQuery be a simpler solution. We are within reason happy to purchase whatever software is required to make this work.

|||

Again, it all depends on how the application is coded. Simply changing from XML to an RDBMS query doesn't guarentee that one will be faster than the other. In other words, you can code an application to be faster in either case.

If performance is key, then for large data sets a database platform might be the way to go. If you need to share data between multiple systems, then XML might be the way to go. It all depends on your needs, but in either case you'll want to evaluate your code to ensure that it is as optimal as possible for your situation.

Benefits of 64 bit

I understand that the 64 environment allows more addressable memory.
Are there any additional performance benefits other than possibly those
gained by increased memory addressability?
Assuming the same database running on a 32 bit environment and a 64 bit
environment and applications hitting both environments with the same
work load. Can the 64 bit environment have a higher throughput. For
this hypothetical case let us assume that advantages of having a bigger
proc cache does not help.
cheers
KenHere is an MS enumeration of the advantages:
http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
We've seen a roughly 40% reduction in query times on a 64bit server vs.
its 32bit equivalent (sql2k5 on both). We're using Intel with 64bit
extensions rather than Itanium.
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput.
This does seem to be the case, though you still have to consider disk
IO limitations (assuming you're not storing your entire database in the
increased RAM on your 64bit server).
Although the performance increases are notable, and MS claims the 64bit
version is fully functional and supported, we have had a few trouble
areas with the 64bit version. SSIS seems quirky, along with
OPENDATASOURCE between 64bit and 32bit servers (not that you'd
necessarily want to use that a lot :))
All-in-all, we're not disappointed.
Have fun!
KenJ|||Proc Cache isn't the main consumer of memory which benefits being on 64 bit.
The main beneficiary is the increased data caching capability & this is by
far the most significant single reason for upgrading to 64 bit, at least
from a performance perspective.
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156990437.923057.219500@.i42g2000cwa.googlegroups.com...
>I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||I agree that the main beneficiary of increased memory is the buffer
cache. Even with 32 bit a large amounts of memory could be addresses
through AWE. Which brings up another question, does 64 bit give a
substantial performance boost by eliminating the need for AWE?
KenJ, you mentioned that query performance increased by 40%, was your
32 bit database memory bound? Do u attribute the improvement to higher
buffer hit rate, reduced IO queue length? I would be very interested in
discussing this further if you are willing.
cheers,
ken|||It appears that you are expecting to gain performance going to 64 bit. That
may very well be the case, but it may just turn out not to be case. The
outcome really depends on your workloads. I have seen the same app coming out
with lower throughput on 64 bit than on 32 bit with the same hardware. You
need to test your app to be sure.
Linchi
"raidken@.yahoo.com" wrote:
> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||using 64b instead of AWE provide a small boost.
if you take a look at the TPC.org web site, you'll see some benchmarks.
a 4CPU server provides 206000tpmC using SQL2005 x64 (128Gb of RAM) and
188000 tpmc with SQL 2000 (64Gb of RAM)
so with having 2times the memory on the server provides only a few advantage
and the x64 version is not so helpfull in this case.
in fact, the difference is also at the disk level, to compensate the lack of
memory the SQL 2000 benchmark use 2 time more disks (total of 30TB versus
15TB)
The advantage for small and mid sizebusiness is the ability to share the
server with multiple applications and let SQL Server to manage more memory
without locking this memory.
the AWE option lock the memory and this memory is not available for other
applications, its good for high performance and dedicated servers. But in a
real world 1 server support more then 1 application (the company install RS,
SQL and AS on the same server to reduce the license cost) locking the memory
is not good. In these conditions the x64bits version is really good, all the
server memory is used but not locked and regarding which application is more
on demand then the server balance the memory usage between them.
But for dedicated servers like intensive transactionnal systems the
advantage is small.
but the big changes are in AS2005 and SSIS where the x64bit platform provide
a huge advantage.
and to finish, because there is no difference in the price and for future
compatibility, use x64 version. There is no disadvantage of the x64 platform
versus the x32, so if you have the choice, use it!!!
also 32bits CPUs will quickly disappeared from the market with the price war
between Intel and AMD.
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||There is some degree of overhead associated with AWE infrastructure, but
it's a relatively small part of the picture.
There's also more to compare than simply whether you can address 32Gb via 32
bit AWE or via 64 bit without AWE. For example, the 64 bit version of SQL
Server 2005 Standard Edition 2005 is limited to 32Gb RAM (on Win 2003 EE 64
bit), whilst the 32 bit version of SQL Server 2005 Standard Edition can't
get anywhere near that amount (I'm not sure what the actual amount is, but I
think it might be 4Gb)
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||Hi Linchi
Any chance you can share what type of workload you've identified that
exhibits these characteristics & how this has been measured?
Regards,
Greg Linwood
SQL Server MVP
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...
> It appears that you are expecting to gain performance going to 64 bit.
> That
> may very well be the case, but it may just turn out not to be case. The
> outcome really depends on your workloads. I have seen the same app coming
> out
> with lower throughput on 64 bit than on 32 bit with the same hardware. You
> need to test your app to be sure.
> Linchi
> "raidken@.yahoo.com" wrote:
>> I understand that the 64 environment allows more addressable memory.
>> Are there any additional performance benefits other than possibly those
>> gained by increased memory addressability?
>> Assuming the same database running on a 32 bit environment and a 64 bit
>> environment and applications hitting both environments with the same
>> work load. Can the 64 bit environment have a higher throughput. For
>> this hypothetical case let us assume that advantages of having a bigger
>> proc cache does not help.
>> cheers
>> Ken
>>|||raidken@.yahoo.com wrote:
> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
Putting aside the benefits for a moment, I'd strongly advise checking
for 64-bit availability of components where necessary. I recently got
dragged in to the later stages of a project where this hadn't been done
and had real trouble getting a 64-bit SQL 2005 installation to link
through to an old Informix box. In the end they had to roll it back to
32-bit.|||Greg;
I can't share it publicly. But if you drop me an email.
Linchi
"Greg Linwood" wrote:
> Hi Linchi
> Any chance you can share what type of workload you've identified that
> exhibits these characteristics & how this has been measured?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...
> > It appears that you are expecting to gain performance going to 64 bit.
> > That
> > may very well be the case, but it may just turn out not to be case. The
> > outcome really depends on your workloads. I have seen the same app coming
> > out
> > with lower throughput on 64 bit than on 32 bit with the same hardware. You
> > need to test your app to be sure.
> >
> > Linchi
> >
> > "raidken@.yahoo.com" wrote:
> >
> >> I understand that the 64 environment allows more addressable memory.
> >> Are there any additional performance benefits other than possibly those
> >> gained by increased memory addressability?
> >>
> >> Assuming the same database running on a 32 bit environment and a 64 bit
> >> environment and applications hitting both environments with the same
> >> work load. Can the 64 bit environment have a higher throughput. For
> >> this hypothetical case let us assume that advantages of having a bigger
> >> proc cache does not help.
> >>
> >> cheers
> >>
> >> Ken
> >>
> >>
>
>

Benefits of 64 bit

I understand that the 64 environment allows more addressable memory.
Are there any additional performance benefits other than possibly those
gained by increased memory addressability?
Assuming the same database running on a 32 bit environment and a 64 bit
environment and applications hitting both environments with the same
work load. Can the 64 bit environment have a higher throughput. For
this hypothetical case let us assume that advantages of having a bigger
proc cache does not help.
cheers
KenHere is an MS enumeration of the advantages:
http://www.microsoft.com/sql/techin...nt.msp
x

> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
We've seen a roughly 40% reduction in query times on a 64bit server vs.
its 32bit equivalent (sql2k5 on both). We're using Intel with 64bit
extensions rather than Itanium.

> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput.
This does seem to be the case, though you still have to consider disk
IO limitations (assuming you're not storing your entire database in the
increased RAM on your 64bit server).
Although the performance increases are notable, and MS claims the 64bit
version is fully functional and supported, we have had a few trouble
areas with the 64bit version. SSIS seems quirky, along with
OPENDATASOURCE between 64bit and 32bit servers (not that you'd
necessarily want to use that a lot )
All-in-all, we're not disappointed.
Have fun!
KenJ|||Proc Cache isn't the main consumer of memory which benefits being on 64 bit.
The main beneficiary is the increased data caching capability & this is by
far the most significant single reason for upgrading to 64 bit, at least
from a performance perspective.
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156990437.923057.219500@.i42g2000cwa.googlegroups.com...
>I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||I agree that the main beneficiary of increased memory is the buffer
cache. Even with 32 bit a large amounts of memory could be addresses
through AWE. Which brings up another question, does 64 bit give a
substantial performance boost by eliminating the need for AWE?
KenJ, you mentioned that query performance increased by 40%, was your
32 bit database memory bound? Do u attribute the improvement to higher
buffer hit rate, reduced IO queue length? I would be very interested in
discussing this further if you are willing.
cheers,
ken|||It appears that you are expecting to gain performance going to 64 bit. That
may very well be the case, but it may just turn out not to be case. The
outcome really depends on your workloads. I have seen the same app coming ou
t
with lower throughput on 64 bit than on 32 bit with the same hardware. You
need to test your app to be sure.
Linchi
"raidken@.yahoo.com" wrote:

> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||using 64b instead of AWE provide a small boost.
if you take a look at the TPC.org web site, you'll see some benchmarks.
a 4CPU server provides 206000tpmC using SQL2005 x64 (128Gb of RAM) and
188000 tpmc with SQL 2000 (64Gb of RAM)
so with having 2times the memory on the server provides only a few advantage
and the x64 version is not so helpfull in this case.
in fact, the difference is also at the disk level, to compensate the lack of
memory the SQL 2000 benchmark use 2 time more disks (total of 30TB versus
15TB)
The advantage for small and mid sizebusiness is the ability to share the
server with multiple applications and let SQL Server to manage more memory
without locking this memory.
the AWE option lock the memory and this memory is not available for other
applications, its good for high performance and dedicated servers. But in a
real world 1 server support more then 1 application (the company install RS,
SQL and AS on the same server to reduce the license cost) locking the memory
is not good. In these conditions the x64bits version is really good, all the
server memory is used but not locked and regarding which application is more
on demand then the server balance the memory usage between them.
But for dedicated servers like intensive transactionnal systems the
advantage is small.
but the big changes are in AS2005 and SSIS where the x64bit platform provide
a huge advantage.
and to finish, because there is no difference in the price and for future
compatibility, use x64 version. There is no disadvantage of the x64 platform
versus the x32, so if you have the choice, use it!!!
also 32bits CPUs will quickly disappeared from the market with the price war
between Intel and AMD.
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||There is some degree of overhead associated with AWE infrastructure, but
it's a relatively small part of the picture.
There's also more to compare than simply whether you can address 32Gb via 32
bit AWE or via 64 bit without AWE. For example, the 64 bit version of SQL
Server 2005 Standard Edition 2005 is limited to 32Gb RAM (on Win 2003 EE 64
bit), whilst the 32 bit version of SQL Server 2005 Standard Edition can't
get anywhere near that amount (I'm not sure what the actual amount is, but I
think it might be 4Gb)
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||Hi Linchi
Any chance you can share what type of workload you've identified that
exhibits these characteristics & how this has been measured?
Regards,
Greg Linwood
SQL Server MVP
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...[vbcol=seagreen]
> It appears that you are expecting to gain performance going to 64 bit.
> That
> may very well be the case, but it may just turn out not to be case. The
> outcome really depends on your workloads. I have seen the same app coming
> out
> with lower throughput on 64 bit than on 32 bit with the same hardware. You
> need to test your app to be sure.
> Linchi
> "raidken@.yahoo.com" wrote:
>|||raidken@.yahoo.com wrote:

> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
Putting aside the benefits for a moment, I'd strongly advise checking
for 64-bit availability of components where necessary. I recently got
dragged in to the later stages of a project where this hadn't been done
and had real trouble getting a 64-bit SQL 2005 installation to link
through to an old Informix box. In the end they had to roll it back to
32-bit.|||Greg;
I can't share it publicly. But if you drop me an email.
Linchi
"Greg Linwood" wrote:

> Hi Linchi
> Any chance you can share what type of workload you've identified that
> exhibits these characteristics & how this has been measured?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...
>
>