Questoin
I am using Sql Server 2000.
I have a table named Cities which has more than 2600000 records.
I have to display the records for a specific city page wise.
I don't want to compromise with performance.
Can anyone has the idea?
Waiting for your fruitful response.
Happy Day And Night For All
Muhammad Zeeshanuddin Khan
Hello Members and Contributors and All the Peoples, Assalamualikum;
I am a Univesity Student and Currently i am creating my own website on ASP.
I have little bit of problem in Personal Message or Private Message Section.
What i want is that my Message Box Field of PM Should render HTML tags.. like here , in this website, Message Box renders HTML tags.. also here it has option to see the HTML view...
ALSO in my SQL Database the maximum length of my PM is 4,000... obviously that field will not store if it increases the limit. So how i increase my PM Field Length. Because i have put the option of replying the message... and old message will be shown as quoted message so all the text is very much important and what technique should use to STOP <a href> tags
I'm a newbie when it comes to the SQL Server side of things. I've been doingAccess on and off for years.I've got a form that is the main point of entry for all data.As a matter of technique, should I use one stored procedure to validate data(testing for uniqueness mostly) then another one to actually write the datato the tables?Does it make a difference one way or another to the performance?Do I gain any flexibility in my app dong it that way (Access Data Project)?Your help is appreciated.--Jake
View 2 Replies View Related
Hi, we currently use binding to bind data from an SQL Query to a DataGrid, the problem is that we offer the choice to the user to choose the field that he want to see in the grid.
We currently include ALL optional field in the query even if they are not used by the grid, that make a realy big query with MANY inner and left outer join.
Whats the best techinique to dynamicaly construct my query with the field that the user choose.
All suggestion or webpage link will be appreciated.
Thanks a lot.
I need to write a query in an SP that returns either yes or no. Is it bad technique to use the return value of the SP for this? Should I use a scalar or output parameter instead? If so which?
Thanks,
Steve
Sorry for my bad englishWhat is the optimization technique used by SqlServer dbms?Thanks
View 1 Replies View RelatedHi AllI'm creating some SPs and I've got a query which is inserting datainto a table with a a unique constraint:CREATE TABLE [fil_Films] ([fil_ID] [int] IDENTITY (1, 1) NOT NULL ,[fil_Film] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_Tfil_Film] PRIMARY KEY NONCLUSTERED([fil_ID]) ON [PRIMARY] ,CONSTRAINT [IX_fil_Films] UNIQUE NONCLUSTERED([fil_Film]) ON [PRIMARY]) ON [PRIMARY]GOWhen I insert data, should I check in the SP to see if there is anexisting record or simply catch the error if it already exists? Whichis the better technique? My current SP looks like:CREATE PROCEDURE spAddFilm (@Type varchar(50)) ASDECLARE @Count intSET NOCOUNT ONSELECT @Count = Count(fil_ID) FROM fil_Films WHERE fil_Films.fil_Film= @TypeIF @COUNT IS NULLBEGININSERT INTO dbo.Tfil_Film (Tfil_Type) VALUES (@Type)RETURN 1 -- OKENDELSEBEGINRETURN 2 -- ExistsENDGOThanksSam
View 2 Replies View RelatedBeen handed a system which inserts millions of records per day into a single table with a composite 'wide' index (they tell me it's the only way to achieve uniqueness) and they run large financial reports from this 300,000,000 row table and of course we are I/O is an understatment.
I wish to split the data feed (inserts) workload away from the reporting tables however I need method to transfer the feed data into the report tables and control the volume of traffic. first choice is replication but is this sufficiently robust enough for a commodeties system? If not any ideas?
Looking for some insight from the professionals about how they handlerow inserts. Specifically single row inserts through a storedprocedure versus bulk inserts.One argument are people who say all inserts (and updates and deletionsI guess) should go through stored procedures. The reasoning is thatthe developers that code the client side have no reason to understandHOW the data is stored, just that it is. Another problem is an insertthat deals with multiple tables. It would be very easy for thedeveloper to forget a step. That last point also applies to businesslogic. In my case, adding a security to our SecurityMaster can touch 1to 4 tables depending on the type of security. Also, certain fieldsare required while others are set to null for depending on the type.Because a stored procedure cannot be passed datasets but only scalarvalues, when you need to deal with multiple (i.e. bulk) rows you arestuck using cursors. This post is NOT about the pros and cons ofcursors. There are plenty of those on the boards (some of themprobably started by me and showing my understanding (or morecorrectly, lack of) of the way to do things). Stored procedures alsogive you the ability to abort and/or log inserts that cannot happenbecause of contraints and/or business rule failures.Another approach is to write code (not accessible from outside thedatabase) that handles bulk inserts. You would need to write in rulesto "extract" or "exclude" rows that do not match constraints orbusiness rules otherwise ALL the inserts would fail because of one badrow. I guess you could put the "potential" rows into a temp table.Apply your rules to the temp table and delete / move rows that wouldfail. Any rows left can that be bulk inserted. (You could also use therows that were moved to another temp table for logging why theyfailed.)So that leaves use with two possible ways to get data into the system.A single row based approach for client apps and a bulk based forinternal use. But that leaves use with another problem. You now havebusiness logic in TWO separate areas. You have to remember to modifycode or fix bugs in multiple locations.For those that are still reading my post, my question is...How do you handle this? What is the approach you take?
View 4 Replies View RelatedI've been asked to turn our single-user database system into a multi-usersystem. At present, each user has a copy of the MSDE on their desktopmachine and uses our program to access it. In future, we would like tocentralise our MSDE instance and allow multiple users to access it. Inorder to facilitate this, we are going to only allow one user write accessto the system at a time (I know, its a kludge, but the system was neverdesigned for multiple users in the first place).I have a single, simple question this being the case: can I update a single"read-only" bit field in a table of the database in order to flag to otherusers that the system is in read-only mode in a way that avoids concurrencyissues? ie. does an "UPDATE" query lock and unlock? ( I suspect the answeris yes! ). If anyone else has experience of these things, I would alsoappreciate some tips on how best to proceed.ThanksRobin
View 7 Replies View Relatedhello.
How to do Search in the method which is done in MSDN library 2005 (local application)?
i.e enabling a search filter and then (most incredibly) as you type the criteria, the
list gets updated depending on the characters entered.
for eg. you type: loo
and obtain the following display:
Look in dialog box
Look tab
lookaheads
l
l
l
l
l
l
l
l
l
l
l
l
l
How to use this fast searching technique in vb.net 2005 and the items to be searched in
sql server 2005 express??
Hi folks, I have implemented this technique to simplify SCD loads and also to maintain consistent units of work during update/insert of a single row. Wanted to get your feedback on this technique: performance, transaction issues, etc.
I send all rows to an OLE DB Command that performs both update and insert for a single row in a single command:
Code Snippet
UPDATE PROPERTY SET ORD_TERM_DT = ? WHERE ACCOUNT_NBR = ? AND ORD_TERM_DT = '9999-12-31 23:59:59';
INSERT INTO PROPERTY (
ACCOUNT_NBR
, APPRAISAL_COMPANY_CD
, .....
, ORD_TERM_DT
) VALUES (?, ...,?);
This way I can guarantee that if the termination (update) of an old row (say, row 10) succeeds, but insert of the new row 10 fails, that it will roll back. Otherwise, row 10 will get terminated without being replaced with a current record...
Performance: load of 7,734 changed records into a table of 6.8M existing records was roughly 8 seconds. The data flow task container TransactionOption = Required.
I'm looking to remove hundereds of legacy procs, triggers, functions etc in a DB. Is there a DMV, sys.<something> command or technique that will tell me the last night an object was accessed or executed?
View 2 Replies View Related
I have Came to know that Pinning concept is removed in sqlserver 2005 .
So is there any alternate for that .Any links regardint that which provides information.
Colleagues:
I'm working through the DM tutorials and I can't select a data mining technique when creating a new mining structure in BI Studio - BI Studio locks up totally. Have installed SQL Svr 20005 SP1 and components from feature pack. Analysis Server service is running, have set the properties in the DM project properties to the running instance of Analysis Services. Have read the various posts on the forum and have double checked my configuration - all seems as it should be.
All suggestions appreciated - very much looking forward to working with the technology.
Michael (michael.dataSense@sympatico.ca)
Hello geniuses
First of all I would like to announce that this is my first time I post here.. However, I'm pretty sure that I'm in the best place to ask what I want. To cut the story short, I'm querying SQL database on a remote machine and having the result saved (mapped) to another table on another database on the same remote machine. The thing is the destination table was empty before the query was run the first time. I have been searching for some smart way so that when I modify the source tables that my query is based on, it doesn't affect except the modified rows. In other words, it should be like if the row is already there, do nothing. otherwise, it updates the existig record. else, it's a new record and it's inserted. I think what i need will include some coding for sure, yes? I don't know if i'm clear about the requirement or not though! but I know that you are experts and can direct me. Waiting for your valuable replies.
Sherif Magdi
Hi All,
We have this SSIS package that serves a ETL role, but sometimes when the package crashes we wanted our support personnel to be aware of it, so we did implement an Event handler Package, executable is the package and Event Handler on taskFailed. The idea is if any of the Tasks fail, then trigger the event handler.
Now recently I found out while debugging the package, one of the DFT's failed, and the control went to the Event handler. But thenrest of the Task's are executing and the package finished successfully.
How do I ensure that if at all some Tasks fail, the control should invoke the task at the event handler and end the package run.
One more query, what is the ideal and best way of Event handling to be followed in case of ETL jobs ?
Thanks in Advance.
Hi,Env: MS SQL Server 2000DB Info (sorry no DDL nor sample data):tblA has 147249 rows -- clustered index on pk (one key ofdatatype(int)) andhas two clumns, both are being used in joins;intersecTbl4AB has 207016 rows -- clustered index on two fks andthis intersection table has six colums but only the two fks are beingused for joins;tblB has 117597 rows -- clustered index on pk (one key ofdatatype(bigint)) andhas four columns but only its key are being used for joinsA complex query involving the above the three tables includes innerand outer joins, aggregate, sorting, predicate, math function, derivedtable etc.On the first run, the query takes about 4200ms to finish;after some research on index optimization, I provided some index hint,then the query runs atabout 3000ms. (That was yesterday).Just now I realized that MS SQL Server 2000 is quite "intelligent", Ithink it saves search termsinto cache because the second time search of the a same term is muchmuch faster. Now, a couple of questions:a) if I construct a long long list of "common" terms andprogrammatically let the sql server to cache them would it speed upthe overal query performance in my case? (Or it may depend on thequality of the "common" terms?) and if your answer is yes (supposedlyyou've been there, do you have to know where I could find such a"common" term list, for everyday life or the general public?)b) what other techniques out there to speed up the above describedquery? Bring it down to 1000ms would be most desirable.Thanks.
View 1 Replies View Relatedthere is any service or technique to reflect database changes to form?
am mean if there is two people update same data and one of them
does the update i need (search) on service in sql server or .net that(or triger) that can automatically reflect changes to the form control that display data automatically when data changes before the another persone make second aupdate, so he can see the update made by the first person pefore he make another update.
Thanks So Much for who try to answer.
I had a problem with the ntext datatype. I need to strip the HTML tags out of a ntext datatype column. I have sample query for that, which works fine for STRING, as stuff is the string function, what to do for ntext field.
=======The Process follows like this =========
--**************************************
--
-- Name: A relational technique to strip
-- the HTML tags out of a string
-- Description:A relational technique to
-- strip the HTML tags out of a string. Th
-- is solution demonstrates how to use simp
-- le tables & search functions effectively
-- in SQL Server to solve procedural / ite
-- rative problems.
-- This table contains the tags to be re
-- placed. The % in <head%>
-- will take care of any extra informati
-- on in the tag that you needn't worry
-- about as a whole. In any case, this t
-- able contains all the tags that needs
-- to be search & replaced.
CREATE TABLE #html ( tag varchar(30) )
INSERT #html VALUES ( '<html>' )
INSERT #html VALUES ( '<head%>' )
INSERT #html VALUES ( '<title%>' )
INSERT #html VALUES ( '<link%>' )
INSERT #html VALUES ( '</title>' )
INSERT #html VALUES ( '</head>' )
INSERT #html VALUES ( '<body%>' )
INSERT #html VALUES ( '</html>' )
go
-- A simple table with the HTML strings
CREATE TABLE #t ( id tinyint IDENTITY , string varchar(255) )
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Some Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css" TYPE="text/css" ></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">
SOME HTML text after the body</HTML>'
)
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Another Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css"></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">Another HTML text after the body</HTML>'
)
go
-- This is the code to strip the tags out.
-- It finds the starting location of eac
-- h tag in the HTML string ,
-- finds the length of the tag with the
-- extra properties if any. This is
-- done by locating the end of the tag n
-- amely '>'. The same is done
-- in a loop till all tags are replaced.
BEGIN TRAN
WHILE exists(select * FROM #t JOIN #html on patindex('%' + tag + '%' , string ) > 0 )
UPDATE #t
SET string = stuff( string , patindex('%' + tag + '%' , string ) ,
charindex( '>' , string , patindex('%' + tag + '%' , string ) )
- patindex('%' + tag + '%' , string ) + 1 , '' )
FROM #t JOIN #html
ON patindex('%' + tag + '%' , string ) > 0
SELECT * FROM #t
rollback
Hi I have a production table in SQL Server 2005 that has approx 500,000 records---every 6 hours this table needs to be truncated and filled
The basic SSIS package uses a script compentant and imports the data into a staging table which has the same structure as the production table. I have a final Execute SQL Task that Truncates the production table and does a Insert into production-select * from stage table.
Takes around 30 seconds to run this last Execute SQL Task--problem is there is a risk that our webservice will query this table during the Execute SQL Task and return incorrect results.
Q1: In this last Execute SQL Task if I used a BEGIN TRANSACTION and COMMIT TRANSACTION; will this be any quicker ?
Q2: In this last Execute SQL Task- would it be better to use a RENAME TABLE technique in TSQL--any code examples ??
Q3:Is there any way in TSQL I can compare EVERY FIELD in two tables ie Stage and Production which have identical data structures and figure out a way to update only the records that changed? Is SQL Server Replication the best way to do this
thanks kindly
Dave
Is there any way to implement a paging scheme such that only the required records are transferred from SQL Server to the asp.net app?
The only support I can find such as the DataAdaptor.Fill will bring all the records back from SQL Server and then create the page...
This obviously still takes time and memory based on the entire query, not the page size.
Any ideas?
I have a table with a lot of records. I want to make paging without passing all the data back to a dataset. I know how to select the top n rows but how do I select 10-20 for example.
View 3 Replies View RelatedHello,
How can I do paging in my SQL Server stored procedure.
I have thought of a way, but I need to :
"SELECT TOP @Count..."
which is not allowed :S
What can I do to get around this?
I have noticed that the server i'm running SQL2k on is starting to page out of the norm. I can see that the regsvc and sqlservr svc are showing high page faults/sec. I have 3 gigs of ram and set the max that sql can use to 2 gigs. It is currently using only 168 MB and still will show high paging at random times. I know I can add more ram but that doesn't seem to be the problem. I have also stopped unnecessary services at the os level.
Any other suggestions to fix this?
Thanks in advance.
Hi there
I've managed to make it query that return a dataset that have 2 views utilising the "Filter" in RS. I treat this as a single record with multiple views.
Now let say if I have a stored precedure that pass 2 parameters one is called state and year and accepting 'ALL' for every possibility of State and Year and construct that into single dataset with 2 views similar like above.
How do I breakdown this in the reporting services so it will have paging?
This is a simple dataset:
RECORDID, ReportViewType, State, Year, VALUE
1, "VIEW1", "NSW", 1, null
1, "VIEW2", null, null, 10000
2, "VIEW1", "NSW", 2, null
2, "VIEW2", null, null, 11000
3, "VIEW1", "VIC", 1, null
3, "VIEW2", null, null, 11003
4, "VIEW1", "VIC", 2, null
4, "VIEW2", null, null, 11001
I would like to break down (paging) this per recordid. Each page obviosuly has 2 views using the same data set with different FILTER.
Do I need to put into a LIST then inside that list put 2 TABLES? Is this possible?!?!
Thanks
I have created a stored proc for paging on a datalist which uses a objectDataSource.
I have a output param itemCount which should return the total rows. Them I am creating a temp table to fetch the records for each page request. My output param works fine if I comment out all the other select statements. But returns null with them. Any help would be appreciated.
CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]( @categoryID int, @pageIndex INT, @numRows INT, @itemCount INT OUTPUT )AS
SELECT @itemCount= COUNT(*) FROM CMRC_Products where CMRC_Products.CategoryID=@categoryID Declare @startRowIndex INT; Declare @finishRowIndex INT; set @startRowIndex = ((@pageIndex -1) * @numRows) + 1; set @finishRowIndex = @pageIndex * @numRows
DECLARE @tCat TABLE (TID int identity(1,1),ProductID int, CategoryID int, SellerUserName varchar(100), ModelName varchar(100), Medium varchar(50),ProductImage varchar(100),UnitCost money,Description varchar(1500), CategoryName varchar(100), isActive bit,weight money)
INSERT INTO @tCat(ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight)SELECT CMRC_Products.ProductID, CMRC_Products.CategoryID, CMRC_Products.SellerUserName, CMRC_Products.ModelName, CMRC_Products.Medium,CMRC_Products.ProductImage, CMRC_Products.UnitCost, CMRC_Products.Description, CMRC_Categories.CategoryName, CMRC_Products.isActive,CMRC_Products.weightFROM CMRC_Products INNER JOIN CMRC_Categories ON CMRC_Products.CategoryID = CMRC_Categories.CategoryIDWHERE (CMRC_Products.CategoryID = @categoryID) AND (CMRC_Products.isActive = 1)
SELECT ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weightFROM @tCat WHERE TID >= @startRowIndex AND TID <= @finishRowIndexGO
I am using a stored procedure to page some objectsThe procedure looks like this:
CREATE PROCEDURE sw20aut.sw20_Kon( @sid_nr INT, @sid_stl INT = 35, @kid int )AS BEGIN SET NOCOUNT ON DECLARE @rader INT, @sid_antal INT, @ubound int, @lbound int SELECT @rader = COUNT(*), @sid_antal = COUNT(*) / @sid_stl FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE kund_id = @kid AND del = '0'
IF @rader % @sid_stl != 0 SET @sid_antal = @sid_antal + 1 IF @sid_nr < 1 SET @sid_nr = 1 IF @sid_nr > @sid_antal SET @sid_nr = @sid_antal SET @ubound = @sid_stl * @sid_nr IF(@sid_antal > 0) SET @lbound = @ubound - (@sid_stl - 1) ELSE SET @lbound = 0 SELECT CurrentPage = @sid_nr, TotalPages = @sid_antal, TotalRows = @rader
DECLARE @ename VARCHAR(64), @fname VARCHAR(64), @konid VARCHAR(64) SET ROWCOUNT @lbound SELECT @ename = enamn, @fname = fnamn, @konid = kon_id FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE kund_id = @kid AND del = '0' ORDER BY enamn, fnamn, kon_id SET ROWCOUNT @sid_stl SELECT kon_id, enamn, fnamn FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE enamn + fnamn + '~' + CAST(kon_id as VARCHAR(64)) >= @ename + @fname + '~' + @konid AND (kund_id = @kid AND del = '0') ORDER BY enamn, fnamn, kon_id SELECT startid = @konid SET ROWCOUNT 0END
The big problem is that i need to display objet with the same name. In my book the best identifier is the PK and therefor i have sorted as above by ordering after LastName, FirstName, ContactId
After som thinking ive reached the conclusion that this dont work if the idnumbers isnt of the same length. as long as they are(for example two people named John Smith, one with id = '23' and one with id = '87' it works. If there ids would have been '23' and '1203' it will not work correctly) of the same length it works fine.
What im wondering is if anyone have a good solution to this? Only thing i can think of is filling all idnumbers with zeros to equal length. Dont know how and if this will affect performance though. Anyone has a practical solution to this?
Hello To make pagination I would like to retrieve only the record from x to y ...I couldn't find how to do to in sql , I was thinking so if there is a way to do it with a sqldatasourceI make my request , put it in a sqldatasource and bind it to my datalistis there a way to "filter the sqldatasource ?" to make what I need ? Thx in advance ?
View 4 Replies View RelatedIm in the process of trying to teach myself SqlServer, comming from Oracle. How the heck do I get the equivlent of %ROWNUM pseudo-column in SqlServer? Top just isn't doing it for me.
Oracle Example wrote:
Select * from foo where foo%ROWNUM > 10 and foo%ROWNUM <20;
I have a paging dilema with an ADO/ASP web based application.
Currently I am using the temp table and inserted index method of paging which works well but the pages that use this paging have a variety of filters on them and a largish subset of data available. This means that every time the page is refreshed the code is creating that temporary table, inserting all the data, selecting the subset and then dropping it.
I was looking for a more efficent way of getting paged data to the client. One of the alternatives I came across was using a server side forward only cursor and the ado getrows() method. This sounds good in princible but I don't know if I am going to get a performance hit by using a server side cursor as opposed to sending the entire recorset to the client and letting it page the results.
Would it be any better to use a stored procedure and pass the full sql statement to it. I can't actually write the sql into the stored procedure becuase it is all totally dynamic.
So I guess I have three options, temp tables, server side cursor and small amounts of data sent to the client or client side cursor and large amounts of data sent to the client.
Any ideas or recomendations?
Is this a correct statement? When commit Charge total (k) is greater than Physical Memory total (k) then the server is paging badly, correct?
thanks.
Hi,
My application runs this query using a stored proc
SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3
Now I want to implement paging for the same using Row_Number so that I can display the results in pages.
Can someone please help me write a query for the same. I tried playing with Row_Number but no luck with it.Basically I am not good with SQL and I had programatically implemented paging in asp.net by looping through all records returned by the query.
Thanks,
Ganesh