Last post Jul 27, 2016 04:02 PM by elbilo
Jul 27, 2016 10:36 AM|skliz4rel|LINK
I am using entity framework and store procedure in my applications. But I notice that during my login process because I am doing a custom login system. Some times queries timeout.
It usually happens in the morning of a brand new day. Then subsequently it could occur. But it more like a norm. When Software testers login on a brand new day login system timesout. I have experience it on my local system on a very rare occassion. This
happens on EF codes and in Stored procedures.
I have been researching on this problem. I discovered I saw suggestions of increasing timeout. Please If I increase timeout in my connection string does it affect stored procedures in any way.
In my Storedprocedure code I call connection string in web.config to section when I want to make a connection to the database.
I have currently increase the Timeout in my database context to 180s. Is this save in any way.
While researching I saw a post which states sometime called
OPTION (OPTIMIZE FOR UNKNOWN)
adding that to query, which will make it less keen to re-use plans blindly.
Please what would that do to a stored procedure code.
I would paste a Stored procedure code of my below I want to know if my SP are save enough they run normally but the question is, are there other things one should add to an SP code to optimize Database for better performance in any way.
See an example of my login SP below
CREATE PROCEDURE GetAdminpasswordsalt
select PasswordSalt from Companyadmin where Username = @Username and Admintype=@Admintype and Client_CompanyID = @CompanyID
Also I notice when creating SP I see some developer use ALTER PROCEDURE and CREATE PROCEDURE
Please what is the difference and when do you use this. I have always used CREATE PROCEDURE in my stored procedures. Well Alter seems like update an already create procedure am I right on this.
have just created a search directory for businesses and organisations. But there is a requirement on my application that requires users or customers to search for products or services.
This businesses on my search directories are given an Online Shop to sell product or services and there other packages.
I want to add a function were by a users can come on a search page looking like the google page and simply search for a particular product for example snickers or so and this search would be intelligent enough to find products based on shop location and display
this products or services just like a google search.
See my fear. A functionality of this kind would take a large load of traffic. Because every body would want a fast search for products or services. I am using Mssql. so on Azure sql is what I deploy to online.
Here comes my question, to run queries to do this search on azure sql would take lot of joins. At least 8 joins or 10 joins for one users query.
Can azure sql handle this ?.
My second option is to use a Nosql db. Of which I installed mongo db. But to do this I resolved to backing up products and services when the shop admin is storing in sql db.
A backup db api stores in Mongo sql so anyting that has to do with product or service search is done on Mongo db. The downside of this approach is. Difficulty to main both db.
Because if a shop admin deletes a product I have to delete from Mongo db too. This also applies to an update too.
Please I need advice, Should I do search on Azure sql db. Or do I backup product and services on Nosql db and let users do a Nosql search on Mongo
Jul 27, 2016 10:58 AM|chilluk|LINK
Addressing the first issue - what is your table structure / number of rows? Do you have indexes on the columns you are filtering / sorting on? Increasing timeout values is usually a mask to an issue not a solution.
Yes ALTER PROCEDURE is the syntax to update an existing stored procedure whereas CREATE PROCEDURE is to make a new one.
I'm not really sure on the Azure SQL questions as I really only use my own dedicated web / db servers (sometimes on Azure but our own virtual boxes with dialled up resources for the task at hand) - I assume that with Azure SQL you can tune the resources
too? I would think unless you are into millions of rows / hits on your db then SQL should be fine to handle to load over other db types.
Jul 27, 2016 02:36 PM|skliz4rel|LINK
I am not using indexing in any way.
May be I used just paste my login modules below. I made a custom principal system with FormsAuthentication module.
int companyID = await CompanyService.ReturnCompanyID_byusername(model.PromptID); // This is a Storeprocedure
bool Approved = await this.CompanyService.IsCompanyapproved(companyID); //This is a Storedprocedure
//2 queries run here below the first would collect PasswordSalt key so the system can encrypt Password string.//The other query would check if Username with hash pass is correct bool checkvalidity = await MembershipService.ValidateUser(model,companyID);//This statement below is a select query collect information of the adminAdminentry adminentry = await this.MembershipService.GetAdminentryinfo_sp(model.Username, model.AdminType, companyID);//This statements below would update Admin login information int activityID = MembershipService.StoreLoginTimeLog(userid, model.AdminType,LoggedIp);bool check = MembershipService.UpdateUsersLoginStatus(model.Username, model.AdminType, true,LoggedIp, companyID);
See were I think the problem is from because I have been reading up on this. the last two statement updates. login information in two different tables.
Possibly they are uncommited transactions when the user is trying to login. So I am trying to merge the two statement with a stored procedure.
Does last 2 lines of statement are EF. But my question is there a way to make update queries work even there are uncommited transactions so no error is thrown.
May be if I start from here I can fix the errors.
Jul 27, 2016 04:02 PM|elbilo|LINK
Increasing the timeout value in your calls to SQL should not affect the results returned from SQL, it just gives SQL more time to run your queries.
If the problem you're encountering is due to the execution time of the stored procedure I would suggest you consider a couple of options;