Need To Cache Or Store These SqlDataSource Objects
May 2, 2008
I have the following:
omegalove.com
If you go to the search portion u will notice that
there are three dropdownlist that are populate using sqldatasource object.
I do not want these two post back. Is there a way we can cache this so it is faster.
I would like to learn. I appreciate any help. Please don't give me links. I much rather learn from someone who has hands on.
Thank you
I will have to separate the code from the ui?---------------------------------------------------------------------
Country:<asp:Image ID="FlagImg" runat="server" Height="12px" Width="20px" />
<asp:DropDownList ID="ddlCountry" runat="server" DataSourceID="SqlDSCountry" DataTextField="Country"
DataValueField="CountryID" AutoPostBack="True" OnDataBound="ddlCountry_DataBound" OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDSCountry" runat="server" ConnectionString="<%$ ConnectionStrings:omegaloveConnectionString %>"
SelectCommand="SELECT [CountryID], [Country] FROM [CountryCodes] ORDER BY [Country]">
</asp:SqlDataSource>
----------------------------------------------------------
State/Province:<br />
<asp:DropDownList ID="ddlRegion" runat="server" AutoPostBack="True"
DataSourceID="SqlDSRegion" DataTextField="Region" DataValueField="RegionID" OnDataBound="ddlRegion_OnDataBound" OnSelectedIndexChanged="ddlRegion_SelectedIndexChanged">
</asp:DropDownList><asp:SqlDataSource ID="SqlDSRegion" runat="server" ConnectionString="<%$ ConnectionStrings:omegaloveConnectionString %>"
SelectCommand="SELECT [RegionID], [Region] FROM [SubCountryCode] WHERE ([CountryID] = @CountryID)">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCountry" Name="CountryID" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
---------------------------------------------------------------
City :<br />
<asp:DropDownList ID="ddlCity" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlCity_SelectedIndexChanged" DataSourceID="DSCity" DataTextField="City" DataValueField="City">
</asp:DropDownList>
<asp:SqlDataSource ID="DSCity" runat="server" ConnectionString="<%$ ConnectionStrings:omegaloveConnectionString %>"
SelectCommand="SELECT [City] FROM [WorldCities] WHERE (([CountryID] = @CountryID) AND ([RegionID] = @RegionID))">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCountry" Name="CountryID" PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="ddlRegion" Name="RegionID" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
---------------------------------------------------------------------------------------------------------------------------------------------------------------
This is my behind code.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using OmegaLove;
using OmegaLove.UI;
namespace OmegaLove.UI.Controls
{
public partial class Search : BaseWebPart
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
FlagImg.ImageUrl = "../images/flags/" + ddlCountry.SelectedValue + ".gif";
ResetRegion();
ResetCity();
}
protected void ddlRegion_SelectedIndexChanged(object sender, EventArgs e)
{
ResetCity();
}
protected void ddlCountry_DataBound(object sender, EventArgs e)
{
ddlCountry.SelectedIndex = 39;
FlagImg.ImageUrl = "../images/flags/" + ddlCountry.SelectedValue + ".gif";
}
protected void ddlRegion_OnDataBound(object sender, EventArgs e)
{
ResetCity();
}
private void ResetRegion()
{
ddlRegion.Items.Clear();
// ddlRegion.DataBind();
if (ddlRegion.Items.Count == 0)
{
ListItem item = new ListItem("Not Available");
ddlRegion.Items.Add(item);
}
}
private void ResetCity()
{
ddlCity.Items.Clear();
// ddlCity.DataBind();
if (ddlCity.Items.Count == 0)
{
ListItem item = new ListItem("Not Available");
ddlCity.Items.Add(item);
}
}
protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
{
}
}
}
View 6 Replies
ADVERTISEMENT
Jun 22, 2015
I am using SQL 2012 and I am trying to find buffer cache usage by DB objects within a particular DB.
I am running the following query
select
name as DB,
objname as db_object_name,
COUNT(name) as cache_page_count,
COUNT('x')*8.0/1024 as size_mb
[Code] ....
Following are the results:-
DB db_object_name cache_page_countsize_mb
TEST_DBNULL 428 3.34375
TEST_DBsysobjvalues 369 2.882812
TEST_DBsyscolpars 44 0.34375
TEST_DBsysssislog 38 0.296875
....
.....
Question- Why am I getting 428 pages for which there is no corresponding DB object? Why are so many pages present in sys.dm_os_buffer_descriptors but are missing from sys.allocation_units.
View 0 Replies
View Related
Sep 25, 2006
I am new to SS2005, and I've just started working on a small test/devdatabase.I recently read that one should store things like tables, views,constraints, etc. in the *.ndf file rather than in the *.mdf file.Does this make it any easier to transfer/copy files or databases orother items from test/dev to production??If I have a database already with items in the *.mdf file, how do Itransfer things like tables, constraints, views, etc. to an *.ndf filein another database??I also read that one can make it the default in SS2005 to store thingsin the *.ndf file, how is this done??Thank you,Tom
View 5 Replies
View Related
Oct 25, 2004
How can we store native .NET objects like arraylists, hashtables in Sql Server? Is this possible? If so, can anybody provide some insight on how to do this?
Thanks
View 4 Replies
View Related
Sep 25, 2006
We are in the midst of cleaning up database01. There are objects in database01 that are widely used in store procedures that reside in database02.
Problem is: I need to know which tables and view from database01 are in use by store procedures in database02. This is important because if I accidently decommission tables that are currently in use by production class s-procedures, it will not be pretty. A sample output could look like:
StoreProc UsingObject
sp001 ctblCodeName
sp002 tblUnitSales
so003 tblSalesTrans
.......... .................
I am not a programmer and none of our programmers here claim that there is any solution to this problem without spending thousands on s/w licenses. Any simple solution + code snippet that will help me resolve this problem by myself would be incredibly valuable.
In disstress and frustration . . . .
JJOSHI
View 4 Replies
View Related
Oct 11, 2006
I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.
I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.
I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?
I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)
tia
John
View 1 Replies
View Related
Apr 24, 2007
I have a label on my page that shows a persons name, but in the database it is identified by a ID for indexing purposes. When I do a UPDATE (users can change other info on the page...not the name though), I want to update by the ID, not the actual name. Problem is, I don't have the ID (just the name) on the page, so I would have to do a lookup for the ID in the database. Is there a another way to do this? When I select the persons name for display using the SqlDataSource, it's easy to select the ID too. But how can I use this ID on the UPDATE? I don't want to reinvent the wheel.
View 1 Replies
View Related
Dec 27, 2007
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data
Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User
Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF"
providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
View 3 Replies
View Related
May 31, 2007
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €œDBCC FLUSHPROCINDB (@dbid)€??
Is there a workaround for €œdbo€? to be able to flush procedure and data cache without being elevated to €œsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans.
Thx.
View 1 Replies
View Related
Feb 15, 2008
Hi guys,
I am looking at the plan caches/cached pages from the perspective of
sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) )
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
a slight change from a query in
http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
Does anyone have any idea what is going on?
View 2 Replies
View Related
Dec 27, 2006
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies
View Related
Nov 20, 2013
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
View 1 Replies
View Related
Mar 13, 2003
On Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL
Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is
consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two
hit ratios, and why would we have such a significant difference between the two?
Any help or advice would be much appreciated.
View 5 Replies
View Related
Jul 23, 2007
What is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
View 7 Replies
View Related
Mar 8, 2007
i am using visual web developer 2005 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
View 1 Replies
View Related
Jul 12, 2006
I read about the SQL 2005 Buffer Cache and the .NET cache.I tried it on about 8000 users query. Both are fast....SQL 2005 being a little faster (probably due to I was sorting the .NET Cache).Any comments?
View 1 Replies
View Related
Jul 9, 2002
Hi there,
We have cluster server and we are using sql server 7, everything was working fine on node a and when we move to node b and run the job from the jobs it gives the error message:
Error 22022:SQLServerAgent Error: job ox7bb7d7bf23b8974d8ea8f15e43e854cf
does not exist in the job cache
Can anyone help on this.
Thanks in advance.
Madhu.
View 3 Replies
View Related
Dec 3, 2001
In Sybase, I can bind a database or table to cache, can I do the same
on SQL 7.0?
View 1 Replies
View Related
Sep 9, 2005
Hi..
I don't know this is possible or not.
for example.. I have a blog site..
my entry links are like that
blog.asp?ID=33
blog.asp?ID=36
36 points "select * tbl where id=36"
And approximately BLOG #36 has 400 views everyday..
content is same.It never change..
Everyday my application performs "select * tbl where id=36"
with direct ASP or Stored Procedure..
Do you have any solution about this ?
How to improve performance..
Just imagine, If entry #36 had 2.000 views everyday ?
just example..
Of course the less perform query, the more performance..
View 1 Replies
View Related
Mar 14, 2008
Hello!
When I create new job, I get a message "Job cache 97% full"
What should I do? And what IS a job cache?
Thanks!
Makkaramestari
View 3 Replies
View Related
Apr 25, 2007
Hi all,
I have a question regarding SQL caching.
I want to use SQL caching for my pages.I've gone through http://msdn2.microsoft.com/en-us/library/e3w8402y(VS.80).aspx article on msdn, where in step by step procedure is explained for this. But I have a slightly different situation. In the article, we need to configure our application from web.config by following...
<!-- caching section group --><caching> <sqlCacheDependency enabled = "true" pollTime = "1000" > <databases> <add name="Northwind" connectionStringName="NorthwindConnectionString1" pollTime = "1000" /> </databases> </sqlCacheDependency></caching>
Where in "NorthwindConnectionString1" is the SQLdatasource connection string. But I'm not using SQLDatasource, and have my own classes to build a connection datasource and get a dataset, and then bind it to my control.So in this case what should I write for the above "connectionstring" attribute. My connection string is in web.config only with a section defined. and in my application i'm reading from web.fing only.The connection section i'm using is as follows...
<ConnSettings>
<add key="ConnString" value="server=serverName;database=dbName;uid=dbuser;Pwd=password"/>
</ConnSettings>
I want to know how to configure my sql cache in this situation.
Thanks,
Mehul Mistry
View 2 Replies
View Related
May 8, 2007
Hello,
Am wondering if someone can give me a pointer on where to start with a query i have.
What i am trying to do is provide some sort of information to a user that data from a database has been changed. I know that you can cache a page and have output cache directive invalidate it next time it loads if changed but wondering if its poss to be told its changed without reload ( dont see how but never know) - even if not on that page.
What im kinda thinking is something like the popup that outlook has when a new message arrives in in box. Is that just sending a new query? is there a table last modified function in sql? etc.
This is for a web app in c#.
Any help would be appreaciated.
cheers
View 3 Replies
View Related
May 31, 2007
Hello all.. i'm having a major issue with my Sql Express 2005 database :(
I'm using wicked code sitemap to allows for a sitemap to be stored in a database.. it uses sql cache dependency to invalidated the cache bla bla
Problem:
After i update a record / add new record the database generates a SqlQueryNotificationStoredProcedure
But it never gets executed. I've done tests on the code it's calling everything accordingly so i'm assuming the problem lies in a configuration setting of the database
done alot of searching around the net and i've found this
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username
i'm using Windows Authentication how do i run this and will this solve the problem
Thank you in advance
Chris
View 3 Replies
View Related
Feb 8, 2008
I'm working off of the example shown here:
http://www.c-sharpcorner.com/UploadFile/mosessaur/sqlcachedependency01292006135138PM/sqlcachedependency.aspx?ArticleID=3caa7d32-dce0-44dc-8769-77f8448e76bc
The tutorial shows that an entry must be made in web.config for a sqlCacheDependency node in web.config. When a dependency is added, they set an attributed called "connectionStringName" that references a connectionString established earlier in the web.config. My question is twofold:
1). First, I tried setting up my connection string using the connectionStrings node (my other apps use the AppSettings node), but when I try to extract the value in my code using the following syntax:
connStr = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["devConnStr"].ConnectionString);
I get the following compilation error: "ConfigurationManager does not exist in the class or namespace System.Configuration";
Am I extracting it with the wrong code? Does that setup exists in .NET v 1.4? No, I cannot use .NET v 2.0 for various reasons.
2). If I cannot use ConnectionStrings in that way and must use AppSettings, how do I set up SqlCacheDependency node to recognize that connection string?
View 1 Replies
View Related
Sep 18, 2000
I have been seeing this strange statistics in one of our servers. The cache hit ratio has gone beyond 100%, it is currently showing 124%. Has anyone seen this before.
Thanks
View 1 Replies
View Related
Nov 1, 1999
I know this might be a dumb one, but what the heck. My new 7.0 server's procedure cache
stays at 100%. After researching this looks like what I want. Nay response appreciated.
View 1 Replies
View Related
Sep 30, 1999
Help, have recently upgraded from 6.5 to 7.0 and have come across a problem with performance. The problem appears to relate to the buffer cache being flushed, the buffer cache hit ratio drops from 98% to 0% in a matter of a second. It then very slowly grows, then is flushed again, then increase slowly upto 30%.
Does any one have any ideas as to what would flush the buffer cache?
Any comments would be much appreciated - cheers
View 1 Replies
View Related
Aug 16, 2000
Hi,
I was wondering if anyone knew where I could find good information on performing a database cache. I want to cache tables and run queries from them within a Cold Fusion Application.
Thanks.
View 1 Replies
View Related
Mar 18, 2005
I have a large dell server with 4 processors, and 8 gig of memory on win 2000 advanced with sql 2000 enterprise edition running a 3rd party app. My cache hit ratio averages about 76%. I thought the gneral rule was if you get below 80% to add more memory. However my memory manager shows I am only using 71% of my memory and have a full gig available. I have the sql server set to use about 7.1 gig of the 8 gig on the server. My ? is if I am only using 71% of my memory, will will adding more memory actually help my cache hit ratio
View 5 Replies
View Related
Jul 28, 1999
Ours is a MSSQL Server Client server application with very minimal usage of Store procedures.
The proc cache is configured at 5%.I execute "dbcc proccache" to keep track of the proc cache.
I have seen that the "proc cache size" reduces to a very small a amount when there is peak usage.
It starts at 42,000 and comes down to 400, though it is always greater than "proc cache used".
I am worried if this causes crashes.
Please advise why this happens and solutions if any.
Thanks in advance,
Ramakrishna seelam.
View 1 Replies
View Related
Feb 11, 1999
Hello folks!
We are going to install SQL server 6.5 with MSMQ.
And set 128M memory for SQL Server.
What % Procedure cach do you recomend to set?
Thanks,
Alona
View 1 Replies
View Related
May 31, 1999
I am doing performance testing and I would like to be able to flush the data cache without re-starting the server. Can this be done?
View 2 Replies
View Related
Jul 9, 2002
Hello,
Can u put table in cache in MS SQL Server?
Thanks
Rea
View 1 Replies
View Related