SQL Server :: How To Do Query Optimization And Performance Tuning
Dec 6, 2010
I have database with 40 big tables.In each stored procedure query is containing minimum 6 joins.The query is taking too much time to execute.I didn't implemented views and indexes.
View 2 Replies
Similar Messages:
Feb 5, 2010
I've been tasked with looking into performance tuning my company's intranet server. It runs DotNetNuke which suffers from poor performance. It also runs a number of other web apps (mix of asp and asp.net).
Could anybody give me a brief list of common performance tweaks that can be made to IIS6?
View 1 Replies
May 24, 2010
Last night I did a load test on a site. I found that one of my shared caches is a bottleneck. I'm using a ReaderWriterLockSlim to control the updates of the data. Unfortunately at one point there are ~200 requests trying to update the data at approximately the same time. This also coincided with CPU usage spikes.
The data being updated is in the ASP.NET Cache. What I'd like to do is if the CPU usage is around 75%, I'd like to just skip the cache and hit the database on another machine.
My problem is that I don't know how expensive it is to create a new performance counter to check the cpu usage. Also, if I would probably like the average cpu usage over the last 2 or 3 seconds. However, I can't sit there and calculate the cpu time as that would take longer than it's taking to update the cache currently.
Is there an easy way to get the average CPU usage? Are there any drawbacks to this?
I'm also considering totaling the wait count for the lock and then at a certain threshold switch over to the database. The concern I had with this approach would be that changing hardware might allow more locks with less of a strain on the system. And also finding the right balance for the threshold would be cumbersome and it doesn't take into account any other load on the machine. But it's a simple approach, and simple is 99% of the time better.
View 1 Replies
Aug 17, 2010
I was wondering if anyone had experience they could share using the Response.IsClientConnected property as a performance optimization for asp.net websites.
The reason I ask is that I am a bit skeptical on how effective it would be in real life scenarios. I understand the concept of checking the value before performing a large task but I just can't see how useful this would be as clients could disconnect at any point time.
View 1 Replies
Jul 19, 2010
How to increase the performance of query in sql server2008
View 4 Replies
Oct 21, 2010
I use slqce database to store master-detail table.It took too long to insert byte array data. how to improvement the query performance?
View 2 Replies
Feb 27, 2011
how to check query performance in sqlserver 2008 r2
View 1 Replies
Mar 11, 2011
isnt there performance analysis tools that i can use to monitor and trouble shoot things like timeout expired (slow query execution)?
I mean is there a way for me to monitor the query performance as it is run from the webpage. It runs perfectly in the SMS but it's slow when it's running from the webpages so it throws Timeout expired exception.
View 7 Replies
Jan 25, 2011
I have one Store Procedure that generating report ..
For storing the data there , i used many temp. tables. There is many Select Query , little less insert and delete query.
Now if there is huge data around 1 lac in temp table my select query taking to much time and also may be insert and delete query
I added Primary key to all auto Increment Field in temp table. Also defined Clustered index on that primary key as unique Clustered index to improve the performance .
But there is not so much improvement in case of huge temp table.
Right now the whole Store Procedure is taking time to complete around 1.5 days or around 30 hours ..
So i want to increase the performance as much that it completes on nearly 3-4 hours.
View 39 Replies
Mar 14, 2011
Say I need to populate 4 or 5 dropdowns w/ items from a database. Each drop down will have < 15 items in it. These items almost never change.
Now I could query the DB each time the page is accessed or I could grab the values from a custom class that would check to see if they already exist in ASP.Net's cache and only if they don't query the DB to update the cache.
It's trivial for me to write but I'm unsure if the performace would be better or not. I think it would be (although not likely anything huge).
View 4 Replies
Feb 16, 2011
I have a query that I want to test the performance of when the query is included directly in the SqlDataSource versus when I call a stored procedure. Does anyone have suggestions on how I would be able to closely watch the actual steps here? I was thinking of turning on tracing and adding Trace.Write for each of the events that fire along the way. This doesn't seem efficient and I wanted to see if there was a better approach.
View 2 Replies
Jun 7, 2010
I am developing a website with huge data which to be stored in SQL Server database. How should I optimize it to make it faster.
1. Using Stored procedures.
2. Functions / Views.
3. Any other methods
View 3 Replies
Sep 20, 2010
I have developed a website in asp.net framework 2 . This website is being hosted in two different servers without any change in code. My issue is about the performance of these 2 sites. One website is taking much time for inserting datas to the DB (SQL server 2005). 2 websites are having different DB server.
I think the issue is for the DB server. How can we rectify the DB performance while insertion and Is there any other cause for this permance issue?
View 1 Replies
Apr 29, 2010
I am not sure if this is the right forum. I can not find a forum for LINQ.
I am working on an application using LINQ. Application performance is not up to par and my tests show that it is LINQ queries that are slow. I was wondering if anybody can recommend where I can find an article about optimizing LINQ performance maybe by compilation or other methods.
View 1 Replies
Mar 20, 2011
I am creating a service oriented application where trying to have everything using services....however there is something I am not sure of , I am having a page that calls the database at the page load...so what would be better and faster?? to call database in pageload , or to call wcf service from javascript during javascript load ??btw , I am using a repeater in the page , but I have created somekind of an engine to create the suitable html so...I'll be creating the repeaters html using the wcf and resend it back to the page If I am using a wcf service at the start.
View 1 Replies
Feb 26, 2010
I have a question related to MVC2, VS2010, AJAX Minifier 4.0 and Build.
I hope it is ok to ask here since it is related to my MVC2 project and probably other people in this forum might have done something like this.
Some time ago I found
SmallSharpTools Packer.NET in
Scott Hanselman's Blog:
I have been using it to minify and combine JS and CSS files. Not when building the MVC application but using a Console Application.
Now I found that Microsoft Ajax Minifier 4.0 already minifies CSS files.
The results seem good and it is a Codeplex project ... So I am planning to move from Packer.NET to Ajax Minifier.
To reduce the number of requests I combine JS files (usually the JQuery Plugins used into a single file JQuery.Plugins.min.js).
It seems Ajax Minifier does not combine files ...
I can include a class on the MVC project which minifies the files using Ajax Minifier and combines the ones that needs to be combined.
The question is: how to include this on the build process of the MVC Application ...
And how to delete the original files on the build version? I only need to have the minified versions after the build ...
View 1 Replies
Feb 11, 2010
i have two models that represent two related tables:
class Article
{
Guid ID;
Guid AuthorID;
string Title;[code]...
i need to show this view model in a view:
class ArticleWithAuthor
{
string ArticleTitle;[code]...
in a controller for Article i need to create a view model for the given article id. i read somewhere that a good way to create a view model is to write a mapper that translates from model to view model.
my question is:since the mapper needs a model to work with, it seems that i need two database requests: one to retrieve my Article model and the other one to get my Person data to merge them in the model view. this seems inefficient. is there a way to combine these requests and still follow this "Entity Translation Pattern"? i know of course that i can create my view model "manually" by writing a custom LINQ expression but i am trying to follow the MVC guidelines.
View 5 Replies
Jul 21, 2010
I am working on ASP.NET website. I need to post a Question Paper based on following parameters.
University
Branch
Subject
At Present My URL is in the below mentioned format
//localhost/MYASP/Posted?PostId=**
My URL should be in the below mention format after URL Rewriting
//localhost/MYASP/Posted/University/Branch/Subject/TITLEOFThePost
let me know the process of finding my querystring after url rewriting
View 2 Replies
Jan 24, 2011
I am currently working on an c# application that minimizes and combines javascript/css asynchronously in the background to load on to the page which completed. Once the combined minimized file(s) are created, they are saved on to disk and all subsequent requests to the page will load this file.
The reason for this is to assist with performance on the page. I have a concern though, what if the combined file is large, eg 200 kb. Would it be better to combine in to 2 files if this was the case and have 2 separate http requests? The file will be gzipped and cached.
View 3 Replies
Feb 3, 2011
I have an application using data from multiple stored procedures in a single database.The dataacess is disconnected one. Entire data is required during loadtime i.e. all calls made at load time. I want to know what is the most optimized way to do it. As the connection is required with only one database,is there a way to create it once and use it for the next times. I am aware of connection pooling but its anyways done by default. kindly suggest some way to optimize the load time. Also the result set from all the procedures cannot be clubbed so multiple stored procedure invoking is compulsory required.
View 1 Replies
May 17, 2010
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
View 10 Replies
Mar 2, 2011
I am trying to create statitics for game downloads and am having dificulty with a sub part of my query. the goup by command is not having the disired result and i think i have been looking at it too long to see what i am doing wrong. [Code]....
At the moment it is producing each result in many rows when only 4 rows should appear. its the download column that is doing it. needs to be sub queried or something to display total in relation to month,gameid,userid.
Month-download-GameID-UserID
1 1
1 1
1 1
1 1
View 2 Replies
Feb 1, 2011
I am trying to display this query result in an aspx page.
It is very slow in loading. Here is the query. The inner query inside the outer quesry is the problem.(Please see the underlined part in the query) - (If I remove that part it is very fast.)
select
top 500
--This column is the issue
,Governing_Class=( case when exists (select top 1 tqc.class_code from
t_quote_class tqc
inner join t_quote_class_premium tqcm on tqc.class_code =tqcm.
class_code
where tqc.appid=pi.appid and tqc.class_code not in('8742' ,'8810','7380')
order by tqcm.premium_amt desc
)
then ( select top 1 tqc.class_code from
t_quote_class tqc
inner join t_quote_class_premium tqcm on tqc.class_code =tqcm.
class_code
where tqc.appid=pi.
appid
order by tqcm.premium_amt desc
)
......... From tables
View 7 Replies
Feb 26, 2011
i have awebsite and i made all my steps to make it in the first links on the search of google (SEO) and now i want to go to google updates to make the steps that apply the website on google after one day almost. can any one tell me what i should begin with or do?
View 7 Replies
Jan 7, 2011
I am working on a large project where I have to present efficient way for a user to enter data into a form.
Three of the fields of that form require a value from a subset of a common data source (SQL Table). I used JQuery and JQuery UI to build an autocomplete, which posts to a generic HttpHandler.
Internally the handler uses Linq-to-sql to grab the data required from that specific table. The table has about 10 different columns, and the linq expression uses the SqlMethods.Like() to match the single search term on each of those 10 fields.
The problem is that that table contains some 20K rows. The autocomplete works flawlessly, accept the sheer volume of data introduces deleays, in the vicinity of 6 seconds or so (when debugging on my local machine) before it shows up.
The JqueryUI autocomplete has 0 delay, queries on the 3 key, and the result of the post is made in a Facebook style multi-row selectable options. (I almost had to rewrite the autocomplete plugin...).
So the problem is data vs. speed. Any thoughts on how to speed this up? The only two thoughts I had were to cache the data (How/Where?); or use straight up sql data reader for data access?
View 1 Replies