|
I have a situation where i need to optimize the query to perform better. possibly a solution.
Here is the situation.
I have a table with the following fields(I am putting only those are significant to the scenirio)
WorkItems[Table]
StartDate
EndDate
Stage
CompletedDate
I need to calculate a value named status according to various conditions involving the above parameters.
Like If StartDate < getdate()
Status = Green
etc..
I created a function called CalculateStatus Passing all the above parameters and added one computed colum in the WorkItems table to call this function.
Since column is non deterministic i cant add a index to this column.
Now when i blindly do a select of status the same is getting calculated very fast
The issue is when i use this computed column with aggragate function like
1) SELECT count(Status) FROM WorkItems
Or when doing a group by
2) SELECT Count(WorkItemId),Status FROM WorkITems
Group BY Status
Could you please guid me how i could optimize the situation so that i can make things faster.
Thanks in advance for your valuable time
Sreehari ARanghatThanks
Laddie
Kindly rate if the answer was helpful
|
|
|
|
|
Instead of a computed column, use a normal column and use an after update/insert trigger on the table that calls your function and sets the column value for the row in question. You then also have the advantage of adding an index on this column.  Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I received the following error (see line *********error***********) when trying to transfer data from excel to sql using the code below. The sql table is in the database and columns all match the store procedure, does anyone have any idea what creates this problem.
Thanks in advance, Michael
Failed to obtain column collation information for the destination table. If the table is not in the current database the name must be qualified using the database name (e.g. [mydb]..[mytable](e.g. [mydb]..[mytable]); this also applies to temporary-tables (e.g. #mytable would be specified as tempdb..#mytable).
void ExcelToSqlBulkCopy(String strPath, String strFile, String strTableName)
{
strExcelStoredProcedure = "SELECT ID,City,State FROM [Cities$]";
String excelConnectionString = y;
String sqlConnectionString = z;
// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand(strExcelStoredProcedure, connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "[MyDatabase].[dbo].[" + strTableName + "]";
bulkCopy.WriteToServer(dr); *********error***********
}
}
connection.Close();
}
}
|
|
|
|
|
Does the table in strTableName exist in the database MyDatabase? And is dbo the owner of the table?Wout Louwers
|
|
|
|
|
bulkCopy.DestinationTableName = strTableName
is sufficient, de database is in the sqlConnectionString
|
|
|
|
|
Hi All,
Does anyone know the sql statement/switch or anything of that nature that I can use to create a case-sensative password field in my sybase DB?
Thanks,
Mel
|
|
|
|
|
|
Passwords should not be case-sensitive, but you're welcome to if you want. You should not be storing clear-text passwords.
I recommend hashing the password (I use SHA-1). That should also allow case-sensitivity. Two birds; one stone.
|
|
|
|
|
That sounds perfect for what I'm looking for.
Do you have an example or a link?
Thanks
|
|
|
|
|
Here are my hashing methods:
public static string
Hash
(
string Subject
)
{
return ( Hash
(
System.Text.Encoding.Unicode.GetBytes ( Subject )
,
new System.Security.Cryptography.SHA1Managed()
) ) ;
}
public static string
Hash
(
string Subject
,
System.Security.Cryptography.HashAlgorithm Provider
)
{
return ( Hash
(
System.Text.Encoding.Unicode.GetBytes ( Subject )
,
Provider
) ) ;
}
public static string
Hash
(
byte[] Subject
)
{
return ( Hash
(
Subject
,
new System.Security.Cryptography.SHA1Managed()
) ) ;
}
public static string
Hash
(
byte[] Subject
,
System.Security.Cryptography.HashAlgorithm Provider
)
{
System.Text.StringBuilder result =
new System.Text.StringBuilder ( Provider.OutputBlockSize ) ;
foreach
(
byte b
in
Provider.ComputeHash ( Subject )
)
{
result.Append ( b.ToString ( "X2" ) ) ;
}
return ( result.ToString() ) ;
}
|
|
|
|
|
| How do you mean 'passwords should not be case-sensitive'. I think they should be! But your'e right about not storing them as clear text. They should be hashed indeed. Wout Louwers
|
|
|
|
|
Nothing in a user interface should be case-sensitive -- that's just my opinion after using OpenVMS for many years and then being thrown into Unix, Windows, VOS, etc. 
Case-sensitivity is user-hostile; it does not serve the user.
|
|
|
|
|
| I never worked with Open-VMS. I think case-sensitve passwords are better for security. Wout Louwers
|
|
|
|
|
PIEBALDconsult wrote: Passwords should not be case-sensitive
I'm glad you're not in charge of security at my bank.
I agree with the suggestion to hash the password in the database. However, be aware that the US Goverment has said that all federal applications should stop using SHA-1 as soon as practical as it is considered too weak and several authors have published attacks which exploit weaknesses in the algorithm.
|
|
|
|
|
Thanks for the feedback guys.
After some discussion it's been asked of me to find out if there is any way of setting the actual column in the database up as a password column in some way. Is there any contraint/switch in sybase that will serve what I'm looking for?
Thanks again
|
|
|
|
|
MWRivera wrote: setting the actual column in the database up as a password column in some way
I'm not sure what you mean by this. Maybe this article will be of help:
http://ianywheresolutions.net/developer/product_manuals/sqlanywhere/1000/en/html/dbdaen10/da-security-s-4649816.html[^]
This obviously leaves you with the problem of how to secure the key used to encrypt and decrypt the password; I for one don't much like the idea of hard-coding it into the trigger like this. But it's a start.
This is for SQLAnywhere, which I think you said you were using. If it is Sybase ASE there is a more sophisticated method for doing this sort of thing which is explained in the documentation.
|
|
|
|
|
Thanks for the link David, I'll check it out at the next opertunity I get.
Mel
|
|
|
|
|
Hi David,
Thanks for the link above. I was wondering if you or anyone else could help with a problem I've came into with it though.
I was able to encrypt the password field using the following trigger when a new record was added to the table:
ALTER TRIGGER "encrypt_new_user_pwd" BEFORE INSERT
ORDER 1 ON "QAS"."tableName"
REFERENCING NEW AS newPwd
FOR EACH ROW
BEGIN
Set newPwd.pwdField = ENCRYPT(newPwd.pwdField , 'key')
END
The problem is I'm unable to decrypt the password of the newly created record (fieldID = 0002), using the following:
SELECT CAST (DECRYPT(pwdField, 'key') AS VARCHAR(100))
FROM "QAS"."tableName" WHERE fieldID = '0002'
In Sybase when trying to execute the above statement I get the following error:
Interactive SQL
The following error occurred while fetching results:
Decryption error: Input must be a multiple of 16 bytes in length for AES
SQLCODE=-851, ODBC 3 State="08001"
Do you have any idea what is going wrong here?
The pwdField is of type VARCHAR and size 15.
Thanks,
Mel
|
|
|
|
|
Hi all
how can i get Primary key column name from table?I find some tips from google but it's not working always show no row selected.
select all_constraints.index_name, all_ind_columns.column_name, all_ind_columns.column_position from all_constraints, all_ind_columns where all_constraints.owner = all_ind_columns.index_owner and all_constraints.constraint_name = all_ind_columns.index_name and all_constraints.constraint_type = 'P' and all_constraints.owner = 'DBO' and all_constraints.table_name = UPPER('VARTABLE') order by all_constraints.index_name asc, all_ind_columns.column_position asc, all_ind_columns.column_name asc
please help me.
|
|
|
|
|
| try hitting the enter key sometime! Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
|
Goood Day All
I have a SSIS package that i have created and i want to execute in C# in my web Application. and i have a Connection manager named "OMEGA" and in my C# am trying to execute it like this
Application app = new Application();
Package package = null;
package = app.LoadPackage(@"C:\O!WEB_SETUP_VERSIONS\SSIS_Compiled\OMEGA.dtsx", null);
Variables vars = package.Variables;
vars["Time1"].Value = time;
vars["Time2"].Value = time;
vars["Time3"].Value = time;
vars["TTBLTYPE"].Value = THREAD_DATA[1].ToString();
ConnectionManager Omega = package.Connections.Add("OLEDB");
Omega.Name = "OLEDBConnection";
String Strcon = obj.GetConnectionString(THREAD_DATA[0].ToString());
Omega.ConnectionString = Strcon;
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
String Log;
if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
{
}
}
}
After it executed the package, when i loop through the exceptions i get this
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.\r\nAn OLE DB record is available. Source: \"Microsoft SQL Server Native Client 10.0\" Hresult: 0x80004005 Description: \"Login timeout expired\".\r\nAn OLE DB record is available. Source: \"Microsoft SQL Server Native Client 10.0\" Hresult: 0x80004005 Description: \"A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.\".\r\nAn OLE DB record is available. Source: \"Microsoft SQL Server Native Client 10.0\" Hresult: 0x80004005 Description: \"TCP Provider: No connection could be made because the target machine actively refused it.\r\n\".\r\n
Please note that i want to use my webconfig not SSIS Config. in the Function GetConnectionString i return a Connection string and Pass it to the package.
ThanksVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
1: Is the connection string correct?
2: Do you have the requisite permissions?
3: Check the SQL configuration via the Sql Server Configuration Manager (was called somehting else in 2005: maybe Surface Area something or other) and ensure that it is set up correctly for your installation.
4: AS I recall the error you have listed is quite common: a quick search on Google would yield plenty of results. Here is one I found in about a second: TCP Provider: No connection could be made because the target machine actively refused it [^].Tychotics: take us back to the moon
"Life, for ever dying to be born afresh, for ever young and eager, will presently stand upon this earth as upon a footstool, and stretch out its realm amidst the stars."
H. G. Wells
|
|
|
|
|
1: The connection string is Correct it comes from my webconfig
2: Can you give me more info on this one. Thanks
3: what Configuration needs to be there ? i have SQl 2005 installed and 2008 installed they both use the same port
4: i saw that links and i have Googled
ThanksVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
