|
I use the SQL SERVER2000. My program is to check the present of the employees, then write the record to the sheet.I will give him/her a tip if he/she has signed today,then I would not write this record to the sheet. How I konw he/she has signed today? Query the large record sheet every time? I think It is time consuming.Is there a better solution?
|
|
|
|
|
If you have recorded logins to a table, add a column for sign-in time. Then use that column in your query to restrict rows to today's information. Also add an index containing both username (or whatever is the column you use to identify the user) and sign-in date.
Also you could delete older rows once in a while.
Another option is only to record the last login date to the table containing user information (update the row and set last login date to getdate()). When you fetch info about the user, check if the last login date is today or earlier.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
i have stored date as varchar(30) data type and Date format is dd/MM/yyyy now i have to convert it into datatime data type can u help me in query ?
i have 330 records and each recod contain the Date in format of dd/MM/YYYY but due to varchar data type i can not perform calculation on Date so i have to convert into datetime without data lose.
please help me in this regard.
Best Regards
Rameez
|
|
|
|
|
|
This question is related to the performance of using Stored Procedure OR Views in the following scenario.
We are building an application for Multiple Organizations (Tenants) and using the Shared Database approach for maintaining the data. So Every Table in our database will have TenantId column. Sample Table is shown here:
CREATE TABLE [ent].[tbl_NetworkProfileTemplate](
[PK_NetworkProfileTemplateId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ProfileTemplateName] [nvarchar](20) NOT NULL,
[ProfileTemplateDescription] [nvarchar](255) NULL,
[FK_TenantId] [int] NOT NULL,
CONSTRAINT [PK_tbl_NetworkProfileTemplate] PRIMARY KEY CLUSTERED
(
[PK_NetworkProfileTemplateId] ASC,
[FK_TenantId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TenantPScheme]([FK_TenantId])
)
Now I want to Protect (filter) the data at the database level: If User belonging to Tenant Id =1 logins into the application that user will see records only for that Tenant. And I want this filtration of data at the Database level so developers writing the code in the business layer doesnot need to worry about the filtration of data.
So there are possibly two solutions for this
1. Create stored procedures for each table to fetch, insert, delete and update the records. The logic in the stored procedure will filter the records based upon the Connection with the Database. (Each Tenant will have separate database User)
2. Create views of each table that will filter out the records based upon the Connection with the database.
Create VIEW [ent].[vw_NetworkProfileTemplate]
AS
SELECT
*
FROM [ent].[tbl_NetworkProfileTemplate]
WHERE FK_TenantId= system_user
I see lot of advantages of using views in my applications
Many applications these days are created where we write inline queries and stored procedures also, so using views does not effect our development of Middle layer too much. Also these days we are using OR Mapper tools so this will be helpful in case of views but not in stored procedures approach.
Some of my company DBA’s told me that there is considerable performance hit and scalability issues when using Views, so use Stored procedures. I did some research on that and see the execution plan for the various queries like fetching the data from these tables using joins with other tables etc. but execution plan is exactly same.
My question is that the view which I have created above is slower or have scalability issues compared to using the stored procedures?
Please answer by looking by my View. This view is very simple and have only one where clause in it.
|
|
|
|
|
Since you have to execute the select statement somewhere, view is not a bad option at all. Basically it's only a stored version of the statement. If you compare the same query inside a view or stored procedure, there's no difference in execution. However, if stored procedure is created using cursors, the stored procedure will be much slower and inefficient.
If you build complex queries over the view, there may be problems for the optimizer to find a good plan. For example, so called predicate pushing may not always be possible. For this reason most of the logic in the statement should be put into the query in the view (if possible).
Another area you should consider is data modifications. Fetching the data through a view is a simple and efficient process, but how are the modifications done. Is the application executing delete, insert and update against the view or do DML statements use another path to the data. When using views there are lots of restrictions that cause the view not to be updatable anymore. For example, joining in view and deleting against the view. Should all the rows be deleted in joined tables or not. You can use INSTEAD OF triggers to overcome these limitations.
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks Mika.
Yes we can use Instead of Triggers where we face problems in delete/ update statements. But I got the idea from your answer that using views doesnot impact any performance as I have to issue the select statement either in Stored Procedure or View.
|
|
|
|
|
No problem 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
i need a sample project as an example of inserting data in a sql database using t-sql on visual studio in webset using asp.net and the form contain textboxs which has the fields of the inserted data ...and it get's inserted into a table in the database
something like registration form
i hope you can help me ... thanks for reading
|
|
|
|
|
Write your code, and if it doesn't work post it here and someone will help you. There are literally thousands of examples both here on CP and Google.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all,
i would to embed my database file(mssql 2005 express) in my exe file. can any one suggest me the way how i do this.
thanks in advance.
rmshah
Developer
|
|
|
|
|
You can't. SQL server need exclusive access to it when it is running as a database.
|
|
|
|
|
|
Hay ..
in SQL server 2005
I can make any select statement , but how can I get the last record :
like that
id invNo
1 a1
2 a2
3 a3
<br />
@newinv nvarchar(10) output<br />
AS<br />
SET NOCOUNT ON<br />
begin<br />
SELECT * FROM table ORDER BY invNo<br />
end<br />
<br />
I want to get the field invNo of the last record then by substring function i can add One ..
I hope my question is clear .
thanks
jooooo
|
|
|
|
|
select top 1 invNo from table order by id desc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Great minds think alike...
|
|
|
|
|
"select top 1 InvNo from table order by id DESC" -- ASSUMES ID IS AN IDENTIY FIELD, OPR AT LEAST THAT THE LAST RECORD HAS THE HIGHEST id
|
|
|
|
|
try this:
Select Max(idno) for last record and Min(idno).
rmshah
Developer
|
|
|
|
|
Hi All,,,
I have a SQL Pervasive 9 database, and have a problem to manage the owner name in it.
How I can Stop the owner name at my project??
thanks all,,
|
|
|
|
|
Have you thought about asking the vendor? I bet they know.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Dear All,
I am trying to put the code for generating an eXcel file using xp_cmdshell into a stored procedure for the purpose of calling it from code.
The code was given by a member of one of the forums sometime back.
USE northwind
EXEC master..xp_cmdshell 'bcp "select ''categoryname'',''description'' union all select categoryname,description from northwind.dbo.categories" queryout "d:\Shared\test1.xls" -c -S"172.16.121.203" -U"sa" -P""'
The above code works brilliantly.
I tried to put it into a stored procedure but getting various errors.The sp creation code is as below:
CREATE PROCEDURE [dbo].[SpWriteExcel]
AS
--create a variable @sql
--DECLARE @SQLStatement varchar(500)
DECLARE @SQLStatement1 varchar(500)
-- @SQLStatement = USE northwind
SELECT @SQLStatement1 = "EXEC master..xp_cmdshell 'bcp select ' 'categoryname' ' ,' 'description' ' union all select categoryname,description from northwind.dbo.categories queryout 'd:\Shared\test2.xls' -c -S\'172.16.121.203\' -U 'sa' -P' ''"
--Execute the SQL statement
--EXEC(@SQLStatement)
EXEC(@SQLStatement1)
GO
The error is with SELECT @SQLStatement1. So I tried to change it in various ways but did not get any success.
One of the ways I changed it to is as below:
SELECT @SQLStatement1 = " EXEC master..xp_cmdshell 'bcp "+"select categoryname,description union all select categoryname,description from northwind.dbo.categories"+" queryout "+ "d:\Shared\test3.xls"+" -c "+"-S"+"172.16.121.203"+"-U"+"sa"+" -P"+""+"'"
Could you pls write a proc for me which contains the above command and which takes the database name, table name and the excel file path as parameters.
Thanks for your help.
|
|
|
|
|
sunny74 wrote: Could you pls write a proc for me
Yes, I could, but I won't. The best solution is to print the sql rather tahn exec it, then you can see whats wrong. Its all part of the learning process, called debugging, and as a developer its something you should be able to do - after all, its what you are being paid for.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
iam using MySql db,
my stored procedure is like this..
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`IS_CITY_EXISTS` $$
CREATE PROCEDURE `IS_CITY_EXISTS`(
IN CITYNAME VARCHAR(45),
OUT AVAILABLE BOOLEAN
)
BEGIN
IF EXISTS(SELECT * FROM city_details WHERE city_name=CITYNAME) THEN
SELECT 1 INTO AVAILABLE;
ELSE
SELECT 0 INTO AVAILABLE;
END IF;
END $$
DELIMITER ;
when iam executing this procedure works fine..
CALL IS_CITY_EXISTS('kak',@Available);
but while exceuting SELECT @AVAILABLE;--IT'S giving result null.
is there any error in procedure..
how can i call this procedure in (c#)..
murali krishna
|
|
|
|
|
I've never used MySQL but two observations still:
- you set delimiter to $$. However you use semicolon after the statements.
- is 0 equal to false in mysql or does it understand false keyword. If so, 0 may be interpreted to null since it's not false.
But then again, just guessing.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Is there anybody show me how to create Function in MySql database?
I need it urgently. 
I have been seeking and trying for 2 days.
but not succeed.
i m trying to extract only number from a given string.
but everytime i try, it show error msg (syntax error)
could anybody has a sample function in mysql database pls?
thanks in advance!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
