Using “EXISTS” makes query faster then using “IN”

There is an alternative way to substitute the using of “In” when querying SQL, which will make the process is faster and more effiecient.
Suppose you are going to get the information of all products which are included in promotion program:
“Select Product_ID, Produc_Description from Products Where Product_ID in (select distinct Product_ID from Promotions)”

The query will display all products from table Products which records are exist in table Promotions.
Now, try to replace “in” with “Exists”:
“Select P.Product_ID, P.Product_Description from Products P where EXISTS (select * from Promotions R where R.Product_ID=P.Product_ID)”

This last query will give the same result as the first one.
But if you work with huge database, the using of “Exist” will be more efficient compare to “In”, because using “in”, the subquery will read all data in table Promotions, then filter them uniquely using “Distinct”. After that, SQL Server will create a temporary table at tempdb for the subquery result, before they are used by main query.

The creation of temporary table, and storing subquery result into it, will make the process of main query suspended. And also, temporary table won’t have an index on it.

Using “Exist” for subquerying will be more efficient, because the subquery will be treated as a part of the main query … and thus, the result will be displayed faster..

Wanna try?

Repair CDRom Drive

You might find, sometime, that your CDRom cannot read the inserted disc.
The disc can be read by other drive, but not yours. Means that the disc is fine, problem is at your drive.
Clean the drive head doesn't help. So... what are you gonna do?

Actually, before you take your CDRom Drive to the repairman, you can try to fix it by your self.
But... there is no guarantee that this action will fix the problem. It is all your risk.

What i did is:
- Take off the drive from the CPU or laptop
- But make sure before you shutdown the computer, the drive is remain open
- Unscrew the drive door, and take it off
- Find flexyble cable that is attached to drive head
- At that area, you'll find something like potensio or screw for trimming
- Using screwdriver, turn it about 10-15 degree clockwise (tighten)
- Put everything back on, and attach the CDRom drive to your computer
- Then insert a disc................ hopefully it works now ^_^

Modifying Windows Registry to Fix Problems Caused by Virus

Working with MS Windows Registry is not a fun thing to do. Only few Windows users play with this. “Regedit” (Registry Editor) is a built-in powerful tool used to edit the registry. If you wrongly modify the registry, your Windows will be crashed, hang or working improperly.

Unfortunately, many viruses work by modifying the Windows Registry, which like or dislike will force you to fix the problem by reconfigure it and set it back to the previous state, because sometime the installed antivirus cannot clean or even detect the virus.

Following are some tips to help you fix Windows problem by editing the registry.
Go to Start > Run, type “Regedit” to bring the Registry Editor up.

1. Cannot Run any Registry Tool, like “Regedit”
Go to Start > Run, type:
“reg delete HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\System /v DisableRegistryTools”
Press
On confirmation prompt, press “Y”.
Now you can run your Regedit

2. Cannot Run “Task Manager”
Go to Start > Run, type:
“reg delete HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\System /v DisableTaskMgr”
Press

or.. if you manage to run Regedit, then you can do this:
Go to Start > Run, type: “Regedit”
Navigate to the following sub key:
[HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\System]
and/or
[HKLM\Software\Microsoft\Windows\CurrentVersion\Policies\System]
Name: DisableTaskMgr
Type: DWORD
If Value is 1 (Restricted) then change it to 0 (UnRetricted) by right clicking on it and select “Modify”, enter “0” on Value Data field, then select “OK”……… or select “Delete” if you want to delete the sub key.

3. Drive C, D or other drive is Hidden
Go to Start > Run, type: “Regedit”
Navigate to the following sub key:
[HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer]
right click on sub key:
Name: NoDrives
Type: DWORD
And select “Delete”. This will make all drives in your computer visible

In Value, you will find Value Data = 1, 2, 4, etc… to tell you which drive is being hidden.
1=Drive A, 2=Drive B, 4=Drive C, 8=Drive D, 16=Drive E, and so on

4. On Explorer: Cannot Right Click at the folder or file
Go to Start > Run, type: “Regedit”
Navigate to the following sub key:
[HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer]
right click on sub key:
Name: NoFolderOptions
Type: DWORD
Then select “Delete”

SQL Server Maintenance

* To Shrink Transaction Log
----------------------------
Open Query Analyzer.
First, Backup the database log using command:
“BACKUP LOG [database name] No_Log”

Then, execute this command:
“DBCC Shrinkfile ([log file], [size])”
[log file] here, is, the Logical name of the database log file, and not physical name.
[size] is the expected size of the database log file.

To get the logical name of the log file, execute this command:
“sp_helpdb [database name]”. This will show you the logical name of the database and database log, and the database file name and database log file name.


* To Check Harddisk Capacity
-------------------------------
Run this Stored Procedure:
“EXEC xp_fixeddrives”

The result will be some kind like this:
Drive MB free
----- -------------
C 18230
D 35690

(2 row(s) affected)