Advice on indexes

I have a table (detail) with 4 columns and over 30 million rows .
Say the columns are called ColA, ColB, ColC, ColD

ColA and ColB form a foreign key as this table is a 'detail' table for another master table in the database (master). The master table uses these two columns as its primary key.
To speed up joins between the two tables I have created an index over ColA and ColB

I frequently need to join the two tables together and so most of my queries are of the form:

SELECT master.*, detail.ColC, detail.ColD FROM master JOIN detail
ON master.ColA = detail.ColA and master.ColB = detail.ColB
WHERE master.ColA = @.valA
ORDER BY master.ColB, detail.ColC

This could return up to 60000 rows for a specific value of @.valA.
This is usually a sub-query that feeds directly into another query or a temporary table.

However, I also quite often need to filter the details further by ColC so I have queries like

SELECT master.*, detail.ColC, detail.ColD FROM master JOIN detail

ON master.ColA = detail.ColA and master.ColB = detail.ColB

WHERE master.ColA = @.valA AND detail.ColC = @.valC

ORDER BY master.ColB

The results set from this could be just a few hundred rows depending on @.valC

I clearly have a requirement to have an index across ColA, and ColB, presumably as the clustered index.

My question is how to speed up queries that involve ColC.
Do I create another index across ColA, ColB and ColC or just ColC by itself?
Will an index just on ColC make use of the the clustered index on the other columns?
Should I make the clustered index cover ColA, ColB and ColC instead ? etc ..

I am using SQL Express and am approaching the 4GB limit and this table and another one similar to it account for 96% of the database size.
The four columns in the detail table are all integer types and so don't take up much space per row. I am worried that a wide index will significantly add to the storage space per row and therefore significantly reduce the amount of data I can store in the database.

Any advice would be appreciated.

You defintely do not ever want to create one index that is a subset of another as you asked, ie. do not create two indexes on

ColA, ColB

ColA, ColB, ColC

The one on ColA, ColB, ColC does everything that the first one does, so it is all you need.

In your case it sounds like a clustered index on ColA, ColB, ColC should be all you need. Leaving out one column won't make much difference to the size. The size will be affected by the fillfactor and the fragmentation level of the index, so you should rebuild the index from time to time (how often depends on the nature of your updates) with a fillfactor of 100%, although a high fillfactor will increase the possibility that some updates will be slower.

|||Thanks for that.

I have done as you have said and created an index across all three columns and I am happy with both the speed of the queries and the size of the database.sql

Advice Needed: Where to put ADO Code

I need some advice on a project that I am working on...

First, here is what I am trying to achieve: A Web Form with two controls: A DropDownList with two items added at design time (Fruits and Vegetables) and an empty ListBox. When the user chooses a "category" from the DropDownList, the ListBox will be populated with a list of either "Fruits" or "Vegetables" retrieved from a SQL database. (Note: Since the data in the SQL database must be converted and formatted programatically, simply databinding the ListBox will not work here.)

I believe that I can do this with the following code (stolen from an MSDN article):

'Create ADO.NET objects.Private myConnAs SqlConnectionPrivate myCmdAs SqlCommandPrivate myReaderAs SqlDataReaderPrivate resultsAs String'Create a Connection object. myConn =New SqlConnection("Initial Catalog=Northwind;" & _"Data Source=localhost;Integrated Security=SSPI;")'Create a Command object. myCmd = myConn.CreateCommand myCmd.CommandText ="SELECT FirstName, LastName FROM Employees"'Open the connection. myConn.Open() myReader = myCmd.ExecuteReader()'Concatenate the query result into a string.Do While myReader.Read() results = results & myReader.GetString(0) & vbTab & _ myReader.GetString(1) & vbLfLoop'Display results. MsgBox(results)'Close the reader and the database connection. myReader.Close() myConn.Close()

Now here is the part that I am not sure about: Is the FormLoad event the best place to put this code? If I do, is this not a lot of overhead (creating, opening and closing a connection) everytime there is a page refresh/PostBack? Would I be better off putting this code in the DropDownList SelectedIndexChanged event? Although that seems like it could make the process of selecting a category take a fairly long time.

Finally, if the is a better way of doing this, I am certainly open to suggestions.

All advice is greatly appreciated.

hello,

you may like to read "ASP.Net Tutorial - If Not Page.IsPostBack" article at,

http://aspnet101.com/aspnet101/tutorials.aspx?id=3

ALSO,

i'd like to suggest you to read this too,

1) "Examining the Data Access Application Block" at,

http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

2) "Working with the Enterprise Library's Data Access Application Block" at ,

http://aspnet.4guysfromrolla.com/articles/030905-1.aspx

regards,

Niraj sikotara.

AdomdConnection permission problem

I am developing a Windows Form application. On one of the forms I display the results of a query to an Analysis Services database. I create the MDX command text by building a string.

Everything works, but users who do not have permission to access the database encounter and access error. I don't want to give all of the users permission to access the database. I want the application to use the credentials in the connection string. However, when I execute the AdomdCommand, it seems to be using the credentials of the logged on user instead of the "User ID" in the connection string.

How can I get the command to use the credentials in the connection string?

Dim cmdText As String = ""

Dim BdSalesBacklog As Decimal = 0

Dim oAdomdConnection As New AdomdConnection("Data Source=server;Catalog=PortalAnalytics;User ID=user;password=password")

Dim oAdomdCommand As AdomdCommand = New AdomdCommand()

Dim oAdomdReader As AdomdDataReader

Dim period As String = ddlPeriod.Items(0)

oAdomdCommand.CommandType = CommandType.Text

cmdText = "" & _

"SELECT " & _

"{ BNBTime.[" & period & "] } ON COLUMNS , " & _

"{ Measures.[Total Backlog Snapshot] } ON ROWS " & _

"FROM BNB "

oAdomdCommand.CommandText = cmdText

Try

oAdomdConnection.Open()

oAdomdCommand.Connection = oAdomdConnection

oAdomdReader = oAdomdCommand.ExecuteReader()

Catch ex As Exception

MessageBox.Show(Err.Description)

End Try

Do While oAdomdReader.Read()

BdSalesBacklog = oAdomdReader.GetDecimal(1)

Loop

oAdomdReader.Close()

oAdomdConnection.Close()

I called a developer friend of mine and he explained to me that SSAS requires the logged on user to have permissions in a Role in the AS database.

I created an AD group containing the users to whom I want to allow access, and I gave that group membership in a new AS Role. I assigned the role the permissions I wanted the users to have.

My application now works for all the users who need to run it.

Thanks

AdomdConnection permission problem

I am developing a Windows Form application. On one of the forms I display the results of a query to an Analysis Services database. I create the MDX command text by building a string.

Everything works, but users who do not have permission to access the database encounter and access error. I don't want to give all of the users permission to access the database. I want the application to use the credentials in the connection string. However, when I execute the AdomdCommand, it seems to be using the credentials of the logged on user instead of the "User ID" in the connection string.

How can I get the command to use the credentials in the connection string?

Dim cmdText As String = ""

Dim BdSalesBacklog As Decimal = 0

Dim oAdomdConnection As New AdomdConnection("Data Source=server;Catalog=PortalAnalytics;User ID=user;password=password")

Dim oAdomdCommand As AdomdCommand = New AdomdCommand()

Dim oAdomdReader As AdomdDataReader

Dim period As String = ddlPeriod.Items(0)

oAdomdCommand.CommandType = CommandType.Text

cmdText = "" & _

"SELECT " & _

"{ BNBTime.[" & period & "] } ON COLUMNS , " & _

"{ Measures.[Total Backlog Snapshot] } ON ROWS " & _

"FROM BNB "

oAdomdCommand.CommandText = cmdText

Try

oAdomdConnection.Open()

oAdomdCommand.Connection = oAdomdConnection

oAdomdReader = oAdomdCommand.ExecuteReader()

Catch ex As Exception

MessageBox.Show(Err.Description)

End Try

Do While oAdomdReader.Read()

BdSalesBacklog = oAdomdReader.GetDecimal(1)

Loop

oAdomdReader.Close()

oAdomdConnection.Close()

I called a developer friend of mine and he explained to me that SSAS requires the logged on user to have permissions in a Role in the AS database.

I created an AD group containing the users to whom I want to allow access, and I gave that group membership in a new AS Role. I assigned the role the permissions I wanted the users to have.

My application now works for all the users who need to run it.

Thanks

ADO/VB6 Glitch

I am connecting a data grid to a vb6 form using the following code (the connection is fine-it connects to a sql server db).

Set rs = New ADODB.Recordset
rs.Open TableName, conn, adOpenKeyset, adLockOptimistic
Set dgPartData.DataSource = rs
dgPartData.Caption = "Event Registration for " & sEventName

The table name is syntactically correct. What happens is if the first record in the database table is selected all is fine. However, if the user scrolls beyond the first record then my form gets all weird with phantom objects appearing through the text boxes, etc..

I have traced the issue to the 'Set dgPartData.DataSource = rs' line. Is there something I should do when connecting a grid to a db in code that I am not doing?

Thanks!What data types used in that table?
How about collation settings on SQL Server and on Windows on App./Web server?|||THe data types are mostly varchar, int and bigint. I am not sure what you mean by collation settings.

I have discovered that it seems to be connected to a bug in the refresh method of the adodc data object that is fired when using the adCmdTable setting and then trying to call the refresh method of the data object.

I have appeared to have worked around it but it is a little awkward. Let me know what you are thinking with the settings questions, please.

Thanks!!|||IF its something to do from VB side then I don't interfere and comment.
As far as SQL server is concerned if you do not find any information from SQL Error log then simply follow the workaround you have adopted.

In general COLLATION setup is used to specify code page and sort order for character data. Where it does deal with Windows collation & SQL Collations, more about this topic can be found from BOOKS ONLINE.

BTW< what is the service pack level on SQL Server & OS?|||I am not sure. How do I find that out? I am using SQL Server 2000 and Windows XP. I am getting the latest updates on the OS automatically. Where do I find the latest SQL Server SPs?

Thanks a ton! I'll look into collation!|||From SQL Server query analyzer run SELECT @.@.VERSION and let me know the result.

You can get information on SPs from MS SQL (http://www.microsoft.com/sql) website and download'em.|||Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)|||should download XP ServicePack 1 and SQL Server Service Pack 3a|||No its SQL 2000 with NO SP!

Refer to the above link and download SP3 and apply, its recommended to fix issues and get rid of hackers.|||aha... do read to SP3a readme file or fixlist before applying SP3a, otherwise SP3 is enough to apply.

ADO.NET connection to SQL fails

Hello,
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO and it is still rejected.
One last question; have you ever wanted to give up with computers and go back to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
Dale
DBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper? =20
>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale
|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/r...eleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:

>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO and it is still rejected.
>One last question; have you ever wanted to give up with computers and go back to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale

ADO.NET connection to SQL fails

Hello,
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm tryin
g to do an insert to can't validate my login. I've verified that the account
does have permissions to access the SQL DB and that everything is granted a
s far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and W
indows).
I've tried all the suggestions from the "connectionstrings.com" website and
I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Se
rver connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO an
d it is still rejected.
One last question; have you ever wanted to give up with computers and go bac
k to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
DaleDBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper? =20
>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/...ReleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:

>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do
an insert to can't validate my login. I've verified that the account does have perm
issions to access the SQL DB and that everything is granted as far as the INSERT, UP
DAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and
Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and
I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL S
erver connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO a
nd it is still rejected.
>One last question; have you ever wanted to give up with computers and go ba
ck to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale